JDBC初学（整理）

JDBC

jdbc是java数据库连接技术，能实现java程序对各种数据库的访问

jdbc连接数据库的步骤

1.Class.forname()加载驱动

2.DriverManager获取Connection连接

3.创建Statement执行SQL语句

4.返回ResultSet查询结果

5.释放资源

安装jdbc

https://mvnrepository.com/artifact/mysql/mysql-connector-java

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.29</version>
</dependency>

步骤实操

1.Class.forname()加载驱动

 Class.forName("com.mysql.jdbc.Driver");

2.DriverManager获取Connection连接

connection = DriverManager.getConnection(
        "jdbc:mysql://192.168.153.141:3306/jdbcstudb", //mysql服务器地址，端口，数据库名
        "root", // 数据库的用户名：root
        "root" //数据库的密码：root
);

3.创建Statement执行SQL语句

//第三步：操作数据库 新增 删除 修改 查询
/*新增*/
String sql = "insert into dog(name,health,love,strain,lytm) value(?,?,?,?,?)";
String name = "旺财";
Integer health = 100;
Integer love = 68;
String strain = "中华土狗";
Date date = new Date(System.currentTimeMillis());
/*创建preparestatement*/
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,name);
preparedStatement.setInt(2,health);
preparedStatement.setInt(3,love);
preparedStatement.setString(4,strain);
preparedStatement.setDate(5,date);

4.返回ResultSet查询结果

//第四步 获取到执行新增语句后的返回结果
int num = preparedStatement.executeUpdate();
if (num > 0){
    System.out.println("新增宠物狗成功");
}

5.释放资源

finally {
   //第五步 释放资源
   try {
       if (connection != null) {
           connection.close();
       }
       if (preparedStatement != null){
           preparedStatement.close();
       }
       if (resultSet != null){
           resultSet.close();
       }
   } catch (SQLException e) {
           e.printStackTrace();
   }
}

执行之后

显示当前时间

改变

String sql = "insert into dog(name,health,love,strain,lytm) value(?,?,?,?,now())";

显示虚拟机的当前时间
然后注释掉//Date date = new Date(System.currentTimeMillis()); //preparedStatement.setDate(5,date);
得出结果

步骤3的另外三种操作

修改

/*修改*/
    String sql = "update dog set name = ? ,health = ? , love = ? where id =?";
    String name = "泰日天";
    Integer health = 100;
    Integer love = 90;
    Integer id = 2;
    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1,name);
    preparedStatement.setInt(2,health);
    preparedStatement.setInt(3,love);
    preparedStatement.setInt(4,id);
    int num = preparedStatement.executeUpdate();
    if (num > 0){
        System.out.println("修改成功！");
    }

结果为：

删除

String sql = "delete from dog where id = ?";
preparedStatement = connection.prepareStatement(sql);
Integer id = 4;
preparedStatement.setInt(1,id);
int num = preparedStatement.executeUpdate();
if (num > 0){
    System.out.println("删除成功");
}

删除前删除后对比

查找

/*查找*/
String sql = "select id,name,health,love,strain,lytm from dog";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
System.out.println("\t\t狗狗信息");
System.out.println("编号\t名字\t健康值\t亲密度\t品种\t领养时间");
while (resultSet.next()){
    System.out.print(resultSet.getInt("id")+"\t");
    System.out.print(resultSet.getString("name")+"\t");
    System.out.print(resultSet.getInt("health")+"\t");
    System.out.print(resultSet.getInt("love")+"\t");
    System.out.print(resultSet.getString("strain")+"\t");
    System.out.println(resultSet.getDate("lytm")+"\t");
}

查找健康值100的泰迪

String sql = "select id,name,health,love,strain,lytm from dog where health =? and strain =?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,100);
preparedStatement.setString(2,"泰迪");