day89 - jdbc连接

jdbc连接

首先创建需要的jdbc测试表

 CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
 ​
 USE `jdbcStudy`;
 ​
 CREATE TABLE `users`(
  `id` INT PRIMARY KEY,
  `NAME` VARCHAR(40),
  `PASSWORD` VARCHAR(40),
  `email` VARCHAR(60),
  birthday DATE
 );
 ​
  INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
 VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
 ('2','lisi','123456','lisi@sina.com','1981-12-04'),
 ('3','wangwu','123456','wangwu@sina.com','1979-12-04')

 

在ide中引入jar包，编写数据库文件

 package lesson1;
 ​
 import com.mysql.cj.jdbc.Driver;
 ​
 import java.sql.*;
 ​
 //第一个程序
 public class jdbc_first_demo {
     public static void main(String[] args) throws ClassNotFoundException, SQLException {
         //1.加载驱动
         Class.forName("com.mysql.jdbc.Driver");//加载驱动
         //2.用户信息和url
         String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
         String username = "root";
         String password = "123456";
         //3.连接成功数据库
         Connection connection= DriverManager.getConnection(url,username,password);
         //4.执行sql对象
         Statement statement = connection.createStatement();
         //5.执行sql的对象，执行sql，返回结果
         String sql = "SELECT * FROM users";
         ResultSet resultSet = statement.executeQuery(sql);
         //返回结果集，封装了我们全部查询出来的结果对象
         while (resultSet.next()){
             System.out.println("id = "+ resultSet.getObject("id"));
             System.out.println("name = "+ resultSet.getObject("NAME"));
             System.out.println("pwd = "+ resultSet.getObject("PASSWORD"));
             System.out.println("email = "+ resultSet.getObject("email"));
             System.out.println("birth = "+ resultSet.getObject("birthday"));
             System.out.println("##############################");
         }
         //6.释放连接
         resultSet.close();
         statement.close();
         connection.close();
 ​
     }
 }

 

获得数据

 id = 1
 name = zhangsan
 pwd = 123456
 email = zs@sina.com
 birth = 1980-12-04
 ##############################
 id = 2
 name = lisi
 pwd = 123456
 email = lisi@sina.com
 birth = 1981-12-04
 ##############################
 id = 3
 name = wangwu
 pwd = 123456
 email = wangwu@sina.com
 birth = 1979-12-04
 ##############################
 ​
 进程已结束,退出代码0

 

代码分析

DriverManager

//1.加载驱动
 Class.forName("com.mysql.jdbc.Driver");//加载驱动
 ​
 //connection代表数据库
 //数据库自动提交，事务提交，事务回滚
 connection.commit();
 connection.rollback();
 connection.setAutoCommit();

 

 

url

//2.用户信息和url
 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
 String username = "root";
 String password = "123456";
 ​
 //mysql -- 3306
 //jdbc:mysql://主机地址:端口号/数据库名？参数1&参数2&参数3

 

 

statement执行sql的对象，PrepareStatement执行sql的对象

 statement.executeQuery();//查询，返回ResultSet
 statement.execute();//执行所有sql
 statement.executeUpdate();//更新，插入，删除，返回受影响的行数

 

 

ResultSet查询的结果集：封装了所有的查询结果

获得数据类型

 resultSet.getObject();//不知道类型时
 resultSet.getInt();//知道类型时
 ......

 

遍历

 resultSet.beforeFirst();
 resultSet.afterLast();
 resultSet.next();
 

 

释放资源

 //6.释放连接
 resultSet.close();
 statement.close();
 connection.close();