Java第二十天：JDBC

一对多

mysql > create table student (
id int primary key auto_increment,
name varchar(20) not null,
age int not null,
gender tinyint(1) not null
);

mysql > create table score_record(
    id int primary key auto_increment,
    stuId int not null,
    javaScore float(5,2) not null,
    cScore float(5,2) not null,
    htmlScore float(5,2) not null
    );
    
    
#1.找出骚磊的所有成绩
mysql > select s.id, sr.javaScore, sr.cScore, sr.htmlScore
from student s, score_record sr
Where s.id = sr.stuId and s.id = 1;

#第二种写法
mysql > select s.id, sr.javaScore, sr.cScore, sr.htmlScore
from student s
inner join score_record sr
on  s.id = sr.stuId
where s.name = "骚磊";


#2.找出骚磊的两次每科平均成绩
mysql > select s.id, s.name, avg(sr.javaScore), avg(sr.cScore),avg(sr.htmlScore)
from student s
inner join score_record sr
on s.id = sr.stuId
where s.name = "骚磊";

#3.找出骚磊考了几次
mysql > select s.id, s.name, count(sr.stuId)
from student s
inner join score_record sr
on  s.id = sr.stuId
where s.name = "骚磊";

JDBC简述

规范！！！
sun公司制定了连接数据库的规范，而且封装了很多类和方法让咱们操作数据库
jdk
	java.sql
	javax.sql
	
	这里从数据库供应商来下载咱们第三方jar
	maven 和mysql官网下载
	
	注意：以后就不用了下载了，因为以后会学一个包管理器的工具
	
	
	导包方式：
		1.（不推荐）
			1.在咱们项目中新建lib文件夹
			2.复制对应的jar包，放到咱们新建的lib
			3.在lib右键  add as libary
		2.以后使用的
			1.菜单栏->file->project structure
			2.project settings-> modules->dependecies->+
			3.JAR or Directors
			4.找到对应的jar
			
			
		
		
			

验证导包是否成功

public class Demo1 {
    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
    }
}

JDBC核心的API（不需要记）

java.sql.Driver  接口
	--| connect(String url, Properties prop)
	url:java连接url的协议
	prop：java连接数据库所需要的属性，通常是以一个文件保存  用户名， 密码，端口号等
	
	
java.sql.DriverManager 类
	--|static registerDriver(java.sql.driver Driver)
	用于注册驱动的方法
	--|java.sql.Connect getConnection(String url, String user, String password )
	获取数据库连接对象的方法
	url:JDBC连接数据库的协议
	user:数据库的用户名
	password:数据库需要的密码
	
	注意：connection对象需要关闭的!!!
java.sql.Connection 接口
	--| java.sql.Statement createStatement();
		获取数据库的搬运工对象，Statement只是把Java中的SQL语句，直接搬运到数据库中执行，该对象也
		是一个资源性质的对象。需要close
	--| java.sql.PreparedStatement prepareStatement(String sql);
		获取数据库预处理搬运工对象，会将传入的SQL语句搬运到数据库中，预处理，能够提高效率和避免最
		基本的【SQL注入】，这也是一个资源性质的对象，需要close

	
java.sql.Statement 接口
	--| int exeuteUpdate(String sql);
		执行传入的SQL语句，主要执行的SQL语句有 create insert update delete drop.. 返回值是int
		类型，返回的数据是 affected rows 受到影响的行数
	--| java.sql.ResultSet exeuteQuery(String sql);
		执行传入的SQL语句，主要执行的就是select DQL语句，返回值是一个java.sql.ResultSet 查询结
		果集对象


java.sql.ResultSet接口
ResultSet也是一个资源型的数据后期，需要关闭的
	--|boolean next();hasnext()百度
	判断当前结果集是否需要继续往下进行遍历
	--|getXXX(int columnIndex)
	//第二个是用的
	--|getXXX(String column)

增加数据使用（搬运工方式）

@Test
    public void testAdd() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbz .bc:mysql://localhost:3306/java1910?useSSL=true";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);

        //准备sql语句
        String sql = "insert into work (name, age, info) values ('骚磊', 66, '很优秀啊！！！')";

        //获取Statement对象
        Statement statement = connection.createStatement();
        //executeUpdate()   操作是 增，删， 改
        //返回值是受影响的行数
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        statement.close();
        connection.close();

删除数据使用（搬运工）

 @Test
    public void testDelete(){
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/java1910?useSSL=true";
            String user = "root";
            String password = "123456";
            //获取数据库的连接对象
            connection = DriverManager.getConnection(url, user, password);
            //准备sql语句是一个删除的sql语句
            String sql = "delete from work where id = 2";
            //获取搬运工对象
            statement = connection.createStatement();
            //使用搬运工对象，将sql语句执行,返回的数据是一个整型，受影响的行数
            int i = statement.executeUpdate(sql);

            System.out.println(i);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

修改数据使用（搬运工）

@Test
    public void testUpdate () throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/java1910?useSSL=true";
        String user = "root";
        String password = "123456";
        //获取数据库连接对象
        Connection connection = DriverManager.getConnection(url, user, password);
        //准备sql语句
        String sql = "update work set name = 'very骚磊' where id = 1";
        //获取搬运工对象
        Statement statement = connection.createStatement();
        //使用搬运工去执行sql语句
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        //关闭资源
        statement.close();
        connection.close();

    }

查询数据使用（搬运工）

@Test
    public void testSelect () throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/java1910?useSSL=true";
        String user = "root";
        String password = "123456";
        //获取数据库连接对象
        Connection connection = DriverManager.getConnection(url, user, password);
        //准备查询的sql语句
        String sql = "select * from work";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        //解析resultset结果集合
        ArrayList<Work> works = new ArrayList<>();

        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String info = resultSet.getString("info");
            works.add(new Work(id, name, age, info));


        }

        for (Work work : works) {
            System.out.println(work);
        }
        resultSet.close();
        statement.close();
        connection.close();


    }

使用jdbc ， PreparedStatement操作数据库（重点） 预处理

数据的插入（使用预处理的搬运工）

@Test
    public void testAdd () throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/java1910?useSSL=true";
        String user = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(url, user, password);

        //1.准备sql语句
        String sql = "insert into work (name, age, info) values(?, ?, ?)";
        String name = "余健";
        int age = 24;
        String info = "爱吃大份加面加肉老碗面";


        //预处理的搬运工，使用conection 去获取预处理的搬运工, 直接放的sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //拿预处理搬运工对象设置参数
        preparedStatement.setObject(1, name);
        preparedStatement.setObject(2, age);
        preparedStatement.setObject(3, info);

        //执行sql语句
        int i = preparedStatement.executeUpdate();
        System.out.println(i);
        preparedStatement.close();
        connection.close();
     
    }

查询一条数据（预处理搬运工）

@Test
    public void testSelectOne () throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/java1910?useSSL=true";
        String user = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(url, user, password);

        String sql = "SELECT * FROM work WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //设置参数
        preparedStatement.setObject(1, 4);
        ResultSet resultSet = preparedStatement.executeQuery();
        Work work = null;
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String info = resultSet.getString("info");
            work = new Work(id, name, age, info);

        }
        System.out.println(work);
        resultSet.close();
        preparedStatement.close();
        connection.close();
        

    }

预处理的查询多个数据

@Test
    public void testSelectAll () throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/java1910?useSSL=true";
        String user = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "select * from work";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<Work> works = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String info = resultSet.getString("info");
            works.add(new Work(id, name, age, info));

        }
        for (Work work : works) {
            System.out.println(work);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();


    }
}

希望大家关注我一波,防止以后迷路,有需要的可以加我Q讨论互相学习java ,学习路线探讨,经验分享与java Q:2415773436