idea中jdbc连接数据库

---

1.引入jar包

在项目名下创建一个名为lib的包，将mysql-connector-java-8.0.23.jar放进去。
在对准这个jar包，点击右键，add as library(作为库添加)。

二、jdbc六步

基本上jdbc连接数据库都是靠这6步的。

public class InsertPerson {
    public static void main(String[] args) throws SQLException,ClassNotFoundException {
//1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取连接
String url="jdbc:mysql://localhost:3306/lvning?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai";
String username="root";
String passwd="123456";
        Connection conn = DriverManager.getConnection(url, username,passwd);
// 3.获取一个发送sql的工具
        String sql="insert into t_person values(1,'岳不群',45,'男','1774322132','华山')";
        PreparedStatement pstm = conn.prepareStatement(sql);

//4.发生并执行sql
        int update = pstm.executeUpdate();
//5.处理结果集
//6.释放资源
        pstm.close();
        conn.close();

    }
}

其中：

获取连接：String
url=“jdbc:所使用的是哪个数据库(mysql或其他)😕/localhost:端口号/数据库名?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai”;

三、JDBC的ORM（对象关系映射）

目录结构如下：

1.entity实体类及对应的表

public class Person {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private String mobile;
    private String address;

    public Person(Integer id, String name, Integer age, String sex, String mobile, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.mobile = mobile;
        this.address = address;
    }

    public Person() {
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", mobile='" + mobile + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

表结构：

DROP TABLE IF EXISTS `t_person`;
CREATE TABLE `t_person`  (
  `person_id` int(11) NOT NULL AUTO_INCREMENT,
  `person_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` tinyint(4) NULL DEFAULT NULL,
  `sex` enum('男','女','其他') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mobile` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`person_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.以.properties结尾的配置文件

在src下创建jdbc.properties文件。
将驱动driver,url,username及passwd都写在配置文件里。

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/lvning?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username = root
password = 123456

3.utils包

public class JDBCutils 
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
    private static Properties prop = new Properties();
    static {
        try {
            //通过流读取jdbc.properties文件中内容
            //通过类对象的getResourceAsStream()获取配置文件输入流
//			InputStream in = new FileInputStream("./src/jdbc.properties");
            // /表示com超级父包所在的目录
            InputStream in = JDBCUtils.class.getResourceAsStream("/jdbc.properties");
            //自动的使用输入流读取配置文件，并将键值对数据保存到本身
            prop.load(in);
            in.close();
            String driverClassName = prop.getProperty("driver");
            Class.forName(driverClassName);
        }catch(Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    //抽取JDBC前2步
    public static Connection getConnection() {

        //从当前线程空间中获取conn
        Connection  conn = tl.get();

        //如果获取不到，说明是第1次获取连接，则从数据库中获取，然后保存到线程中
        if(conn == null) {
            try {

                String url = prop.getProperty("url");
                String user = prop.getProperty("username");
                String password = prop.getProperty("password");
                conn = DriverManager.getConnection(url, user, password);
                tl.set(conn);
            }catch(Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        //conn不为空，说明不是第1次，则直接返回从线程对象中获取的连接
        return conn;
    }

    //抽取JDBC最后1步
    public static void close(Connection conn,ResultSet rs,PreparedStatement pstm) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstm != null) {
            try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        Connection connection = getConnection();
        System.out.println(connection);
    }
}

4.dao包及Impl包

public interface PersonDao {
    int insertPerson(Person p);

    int deletePersonById(Integer id);

    int updatePerson(Person p);

    Person selectPersonById(Integer id);

    List<Person> selectAllPerson();
}

实现类：

public class PersonDaoImpl implements PersonDao {
//
    @Override
    public int insertPerson(Person p) {
        Connection conn = JDBCutils.getConnection();
        String sql="insert into t_person values (null ,?,?,?,?,?)";
        PreparedStatement pstm = null;
        int update = 0;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,p.getName());
            pstm.setInt(2,p.getAge());
            pstm.setString(3,p.getSex());
            pstm.setString(4,p.getMobile());
            pstm.setString(5,p.getAddress());

            update = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCutils.close(conn,null,pstm);

        return update;

    }

    @Override
    public int deletePersonById(Integer id) {
        Connection conn = JDBCutils.getConnection();
        PreparedStatement pstm = null;
        int update=0;
        try {
            String sql="delete from t_person where person_id=?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
             update = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCutils.close(conn,null,pstm);
      return update;
    }

    @Override
    public int updatePerson(Person p) {
        Connection conn = JDBCutils.getConnection();
        String sql="update t_person set person_name=? ,age=? where person_id=?";
        PreparedStatement pstm = null;
        int update = 0;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,p.getName());
            pstm.setInt(2,p.getAge());
            pstm.setInt(3,p.getId());
            update = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCutils.close(conn,null,pstm);
        return update;
    }

    @Override
    public Person selectPersonById(Integer id) {
        Connection conn = JDBCutils.getConnection();
        String sql="select * from t_person where person_id=?";
        PreparedStatement pstm = null;
        Person person= null;
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            ResultSet rs = pstm.executeQuery();
            person = null;
            while (rs.next()) {
                int id1 = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                String sex = rs.getString(4);
                String mobile = rs.getString(5);
                String address = rs.getString(6);
                person = new Person(id1, name, age, sex, mobile, address);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCutils.close(conn,null,pstm);
        return person;
    }

    @Override
    public List<Person> selectAllPerson() {
        Connection conn = JDBCutils.getConnection();
        String sql="select * from t_person";
        List<Person> persons = new Vector<>();
        Person person=null;
        ResultSet rs=null;
        PreparedStatement pstm=null;
        try {
             pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();

        while (rs.next()){
        int id = rs.getInt(1);
        String name = rs.getString(2);
        int age = rs.getInt(3);
        String sex = rs.getString(4);
        String mobile = rs.getString(5);
        String address = rs.getString(6);
         person = new Person(id, name, age, sex, mobile, address);
        persons.add(person);
    }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCutils.close(conn,rs,pstm);
        return persons;
    }
}

5.测试类

public class Test {
    public static void main(String[] args) {
        PersonDaoImpl personDao = new PersonDaoImpl();
//        int i = personDao.deletePersonById(3);
//        System.out.println(i);
//        List<Person> persons = personDao.selectAllPerson();
//        System.out.println(persons);
//        Person person = new Person(null, "江宁", 18, "男", "123xxxxxx", "华山");
//        Person person = new Person(5, "任我行", 30, "男", "123xxxxxx", "未知");
//        int i = personDao.insertPerson(person);
//        System.out.println(i);
//
//        int i = personDao.updatePerson(person);
//        System.out.println(i);
        Person person = personDao.selectPersonById(1);
        System.out.println(person);
    }
}