JDBC驱动加载

JDBC

1. JDBC流程

   1. 加载驱动:Class.forname(“数据驱动”);
      JDBC所用jar包下载

   2. 获取连接:Connection con = DriverManage.getConnetion();

      • package com.zf.dao;
        import com.zf.pojo.User;
        import com.zf.util.DButil;
        
        import java.sql.Connection;
        import java.sql.PreparedStatement;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.util.List;
        public class UserDaoImple implements UserDaoIf {
            Connection con = null;
            PreparedStatement pst = null;
            ResultSet rs = null;
            @Override
            public boolean addUser(User u) {
                try {
                    //1.加载驱动  2.获取连接
                    con = DButil.getCon();
                    //3.获取执行sqlstatement对象
                    pst = con.prepareStatement("insert into user values(null,?,?,?,?,?,?,?)");
                    pst.setString(1,u.getUserName());
                    pst.setString(2,u.getPassWord());
                    pst.setString(3,u.getName());
                    pst.setInt(4,u.getAge());
                    pst.setString(5,u.getSex());
                    pst.setString(6,u.getPhone());
                    pst.setString(7,u.getAddress());
                    //4.执行sql语句
                    int i = pst.executeUpdate();
                    if(i>0){
                        return true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    //6.关闭资源
                    DButil.dbClose(con,pst,rs);
                }
                return false;
            }
        
            @Override
            public List<User> getAllUser() {
                return null;
            }
        
            @Override
            public User getUserById(int uid) {
                return null;
            }
        
            @Override
            public boolean upUser(User u) {
                return false;
            }
        
            @Override
            public boolean delUserById(int uid) {
                return false;
            }
        }
        
        

   3. 获取执行sql语句的statement对象:com.prepareStatement(“sql语句”);

      •  package com.zf.pojo;
        
        import java.util.Objects;
        
        public class User {
            private int uid;
            private String userName;
            private String passWord;
            private String name;
            private int age;
            private String sex;
            private String phone;
            private String address;
            //alt + ins
            public User(int uid, String userName, String passWord, String name, int age, String sex, String phone, String address) {
                this.uid = uid;
                this.userName = userName;
                this.passWord = passWord;
                this.name = name;
                this.age = age;
                this.sex = sex;
                this.phone = phone;
                this.address = address;
            }
        
            @Override
            public String toString() {
                return "User{" +
                        "uid=" + uid +
                        ", userName='" + userName + '\'' +
                        ", passWord='" + passWord + '\'' +
                        ", name='" + name + '\'' +
                        ", age=" + age +
                        ", sex='" + sex + '\'' +
                        ", phone='" + phone + '\'' +
                        ", address='" + address + '\'' +
                        '}';
            }
        
            @Override
            public boolean equals(Object o) {
                if (this == o) return true;
                if (o == null || getClass() != o.getClass()) return false;
                User user = (User) o;
                return uid == user.uid &&
                        age == user.age &&
                        Objects.equals(userName, user.userName) &&
                        Objects.equals(passWord, user.passWord) &&
                        Objects.equals(name, user.name) &&
                        Objects.equals(sex, user.sex) &&
                        Objects.equals(phone, user.phone) &&
                        Objects.equals(address, user.address);
            }
        
            @Override
            public int hashCode() {
                return Objects.hash(uid, userName, passWord, name, age, sex, phone, address);
            }
        
            public int getUid() {
                return uid;
            }
        
            public void setUid(int uid) {
                this.uid = uid;
            }
        
            public String getUserName() {
                return userName;
            }
        
            public void setUserName(String userName) {
                this.userName = userName;
            }
        
            public String getPassWord() {
                return passWord;
            }
        
            public void setPassWord(String passWord) {
                this.passWord = passWord;
            }
        
            public String getName() {
                return name;
            }
        
            public void setName(String name) {
                this.name = name;
            }
        
            public int getAge() {
                return age;
            }
        
            public void setAge(int age) {
                this.age = age;
            }
        
            public String getSex() {
                return sex;
            }
        
            public void setSex(String sex) {
                this.sex = sex;
            }
        
            public String getPhone() {
                return phone;
            }
        
            public void setPhone(String phone) {
                this.phone = phone;
            }
        
            public String getAddress() {
                return address;
            }
        
            public void setAddress(String address) {
                this.address = address;
            }
        }
         
          
        
        package com.zf.util;
        import org.apache.commons.dbcp.BasicDataSource;
        
        import java.io.IOException;
        import java.sql.Connection;
        import java.sql.PreparedStatement;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.util.Properties;
        
        public class DButil {
            private static String driver;
            private static String url;
            private static String user;
            private static String pass;
            private static BasicDataSource ds;
            static {
                Properties ps = new Properties();
                try {
                    ps.load(DButil.class.getClassLoader()
                            .getResourceAsStream("db.properties"));
                    driver = ps.getProperty("driver");
                    url = ps.getProperty("url");
                    user = ps.getProperty("user");
                    pass = ps.getProperty("pass");
        
                    ds = new BasicDataSource();
                    ds.setDriverClassName(driver);
                    ds.setUrl(url);
                    ds.setUsername(user);
                    ds.setPassword(pass);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            public static Connection getCon() throws SQLException {//1.记载驱动 2.获取连接
                return ds.getConnection();
            }
            public static void dbClose(Connection con, PreparedStatement pst, ResultSet rs){//6关闭资源
                if(con!=null){
                    try {
                        if(!con.isClosed()){
                            con.close();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if(pst!=null){
                    try {
                        pst.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if(rs!=null){
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            public static void main(String[] args) throws SQLException {
                System.out.println(DButil.getCon().getClass().getName());
            }
        }
        
        

   4. 执行sql语句:executeUpdate();返回影响行数 executeQuery();返回结果集ResultSet

      • package com.zf.dao;
        import com.zf.pojo.User;
        import java.util.List;
        public interface UserDaoIf {
            //添加用户
            public boolean addUser(User u);
            //查询所有用户
            public List<User> getAllUser();
            //根据id查询用户
            public User getUserById(int uid);
            //修改用户
            public boolean upUser(User u);
            //删除用户
            public boolean delUserById(int uid);
        }
        
        
        #测试
            import com.zf.dao.UserDaoImple;
        import com.zf.pojo.User;
        
        public class Test {
            public static void main(String[] args) {
                UserDaoImple dao = new UserDaoImple();
                User u = new User(0, "admin", "123456",
                        "张三", 20, "男", "110", "甘肃兰州");
                boolean b = dao.addUser(u);
                System.out.println(b);
            }
        }
        
        

   5. 处理结果集:ResultSet.next() 获取结果集中的内容

   6. 关闭资源:close();