JDBC学习

张三分557

于 2022-09-19 16:28:46 发布

阅读量290

点赞数

文章标签：学习数据库 java

本文链接：https://blog.csdn.net/weixin_72791146/article/details/126933329

版权

JDBC(Java Database Connectivity)是java连接数据库的规范,可以使用Java语言连接数据库完成增删查改操作.

JDBC的核心思想是Java中定义了访问数据库的接口,可以为多种关系型数据库提供统一的访问方式,有数据库厂商提供驱动实现类.

JDBC操作步骤

1.导包

导入相应的驱动jar包.

2.注册驱动

使用Class.forName("com.mysql.jdbc.Driver");手动加载字节码文件到JVM中.

3.连接数据库

通过DriverManger.getConnection(url,user,password)获取数据库连接对象

URL:jdbc:mysql://localhost:3306/database

username:root

password:12356

URL:统一资源定位符,由协议,IP,端口,sid(程序实例名称)组成.

4.准备sql语句

String sql=" ";

数据库DML语句

插入语句:insert into 表名(name,age,gender,address)values('库里','35','男','旧金山')

修改语句:update 表名set name='汤普森',age=33,address='洛杉矶' where id=1=2

删除语句:delete from 表名 where id=2;

数据库DDL语句(建表)

建立球员表:create table player( id int primary key auto_inscrement,name varchar(10),age int,gender varchar(3),address varchar(20))

数据库DQL语句(查询)

select id='编号','name '姓名', age'年龄',gender '性别',address '地址'from 表名

5.通过连接对象获取执行对象Statement

Statement stmt= conn.createStatement();

Statement弊端:

1)存储在字符串拼接sql语句,不安全.

2)执行效率低,频繁和数据库交互.

因此引入PreparedStatement(预编译对象),是Statement子接口.

PreparedStatement特点:

1)不存在字符串拼接.

2)执行效率高.

//获取执行对象---->预编译对象PreparedStatement
//PreparedStatement prepareStatement(String sql):将上面的参数化sql发送给数据库
//将sql语句存储到了预编译对象中
        PreparedStatement stmt = conn.prepareStatement(sql);

//给占位符赋值
//通用setXXX(占位符号索引值,实际值) ; // 占位符号索引值是从1开始,第一个?就是1,第二个?就是 2
        //举例:setString(1,username)
        stmt.setString(1,username);
        stmt.setString(2,password);

6.执行sql语句

executeQuery:一般执行DQL查询语句，返回ResultSet.

ResultSet rs = stmt.executeQuery();
System.out.printIn(rs);

executeUpdate:一般执行DML语句(增删改)DDL语句(建表)（这些操作更新数据库,所以是update）,返回int,被改变的语句的行数。

int count=stmt.executeUpdate(sql)
System.out.printIn(count);

execute，不确定是什么类型的SQL语句时可以用这个方法。

7释放资源

public class JDBCDemo {
    public static void main(String[] args) throws Exception {

        //1)导入驱动mysql的jar包
        //2)注册驱动
        Class.forName("com.mysql.jdbc.Driver") ;

       // DriverManager.registerDriver(new com.mysql.jdbc.Driver());

        //3)创建数据库的连接对象Connection
        //jdk提供的DriverManager:驱动管理类(管理jdbc的驱动的)
        //public static Connection getConnection(String url,String user,String password)throws SQLException
        //参数url: 统一资源定义符号  组成  协议:端口号:库名   --->jdbc:mysql://localhost:3306
        //参数user: 登录msyql的用户名 root用户
        //参数password:登录mysql的密码
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/ee_2208_02",
                "root",
                "123456"
        );


        //4)准备好sql
        String sql = "insert into account(name,balance) values('库里',2000)  " ;

        //5)通过连接对象获取执行对象Statment
        //Statement createStatement()throws SQLException创建一个Statement对象，用于将SQL语句发送到数据库
        Statement stmt = conn.createStatement();

        //6)执行sql语句   (插入操作)
        //Statement执行器里面---->
        //int executeUpdate(String sql)throws SQLException 通用的执行通用添加,删除,修改
        int count = stmt.executeUpdate(sql);
        System.out.println("影响了"+count+"行") ;

        //7)释放资源 (系统资源需要被释放的)
        stmt.close();
        conn.close();
    }
}

封装工具类

在实际JDBC的使用中,存在大量重复代码,因此可以将传统的JDBC代码重构,抽取出通过的工具类,以后连接任何数据库,释放资源都可以使用这个工具类.

封装获取连接,释放资源的两个方法:

public static Connection getConnection(){}

public static void closeAll(Connection conn,Statement stmt,ResultSet rs){}

跨平台工具类实现

在src目录下新建db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb
user=root
password=1234

1.JdbcUtils

public class JdbcUtils {

    //提供几个属性
    private static String url = null ;
    private static String user = null ;
    private static String password = null ;
    private static String driverClass= null ;
    //构造方法私有化
    private JdbcUtils(){}

    //提供静态代码块
    //JdbcUtils 类一加载,static{}就加载了,
    static{

        try {
            //1)读取src下面的配置文件jdbc.properties
            InputStream inputStream = JdbcUtils.class.getClassLoader().
                    getResourceAsStream("jdbc.properties");
            //2)将配置文件的内容加载到属性集合列表Properties
            //创建一个空的属性集合列表
            Properties prop = new Properties() ;
            prop.load(inputStream);
            //System.out.println(prop);
            //3)通过配置文件的key获取value
            //4)就给上面的成员变量赋值
            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url") ;
            user = prop.getProperty("user") ;
            password = prop.getProperty("password") ;

            //注册驱动
            Class.forName(driverClass) ;

        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    //对外提供静态方法
    //定义一个方法:获取连接对象
    public static Connection getConnection(){

        try {
            //需要用到驱动管理类,获取连接对象
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn ;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return  null ;
    }

    /**
     * 释放资源 针对DQL语句,select语句
     * @param rs  释放结果集对象
     * @param stmt 释放执行对象
     * @param conn 释放连接对象
     */
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 释放资源,针对DML语句:update,delete,insert操作的
     * @param stmt 释放执行对象
     * @param conn 释放连接对象
     */
    public static void close(Statement stmt,Connection conn){
       close(null,stmt,conn);//复用上面的close,第一个参数为null
    }


    public static void main(String[] args) {

        Connection connection = JdbcUtils.getConnection();
        System.out.println(connection);
    }


}

2.德鲁伊连接池(Druid)

1.导入德鲁伊jar包

2.准备好连接池配置文件

3.创建连接池对象

4.创建数据源

jar包

public class JdbcUtils_WithDruid {

    //成员变量的位置
    private static ThreadLocal<Connection> t1 = new ThreadLocal<>() ;//类一加载,创建线程
    private static DataSource ds ; //数据源---->里面空的
    //无参构造方法私有化
    private JdbcUtils_WithDruid(){}
    //静态代码块
    static{
        try {
            //1)读取src下面的 德鲁伊的配置文件
            //创建集合列表
            Properties prop = new Properties() ;
            //读取druid.properties
            InputStream inputStream = JdbcUtils_WithDruid.class.getClassLoader().
                    getResourceAsStream("druid.properties");

            //将资源文件输入流中的内容加载到属性列表中
            prop.load(inputStream);
            System.out.println(prop);


            //2)将配置文件进行加载---DruidDataSource自动封装这些7个参数
            //通过DruidDataSourceFactory 创建数据源
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //定义方法----获取数据源的方法
    public static DataSource getDataSource(){
        return  ds ;
    }

    //定义一个获取连接对象
    public static Connection getConnection(){
        Connection conn = null ;
        try {
            //现在模拟多线程场景
            //1)从当前线程获取连接对象   ThreadLocal<Connection>----> Connection get()
           conn  = t1.get();
            //2)判断当前线程中的连接对象如果为null,
            if(conn==null){
                //说明没有连接对象
                //从连接池中获取连接对象
                Connection connection = ds.getConnection();
                //将连接池获取连接对象,绑定在当前线程中
                //ThreadLocal<Connection>----> set(Connection conn)
                t1.set(connection);
                return connection ;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return  conn ;

    }



    /**
     * 释放资源 针对DQL语句,select语句
     * @param rs  释放结果集对象
     * @param stmt 释放执行对象
     * @param conn 释放连接对象
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();//释放--->归还给连接池
                //从当前线程中解绑
                //ThreadLocl--->remove()
                t1.remove();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 释放资源,针对DML语句:update,delete,insert操作的
     * @param stmt 释放执行对象
     * @param conn 释放连接对象
     */
    public static void close(Statement stmt,Connection conn){
        close(null,stmt,conn);//复用上面的close,第一个参数为null
    }


    public static void main(String[] args) {
        DataSource dataSource = JdbcUtils_WithDruid.getDataSource();
        System.out.println(dataSource);

        Connection connection = JdbcUtils_WithDruid.getConnection();
        System.out.println(connection);
    }
}

Java连接数据库的方式(JDBC)控制事务

java.sql.Connection接口
void setAutoCommit(boolean autoCommit) throws SQLException
是否开启手动提交,参数true,就是自动提交,false,禁用自动提交,手动提交----->相当于mysql的指令 start transaction;
void rollback() throws SQLException撤销在当前事务中所做的所有更改----->事务回滚方法---->相当于msyql指令 rollback;
void commit()throws SQLException使上次提交/回滚之后所做的所有更改都将永久性  (提交事务)---->mysql的指令 commit;
Jdbc控制事务:当某个业务执行过程中需要同时指定多个sql(添加/删除/修改)需要将这个多个sql看成一个整体,他们要么同时执行成功,要么同时执行失败

转账实例


public class JdbcDemo {
    public static void main(String[] args) {

        //不使用JDBC控制事务

        //现在使用JDBC控制事务
        Connection conn = null ;
        PreparedStatement ps = null ;
        PreparedStatement ps2 = null ;
        try {
            //获取数据库连接对象
            conn = JdbcUtils.getConnection();
            //开启事务
            //void setAutoCommit(boolean autoCommit) throws SQLException
            conn.setAutoCommit(false) ;//禁用自动提交
            //sql语句
            String sql1 = "update account set balance = balance -? where id= ? " ;
            String sql2 = "update account set balance = balance +? where id = ?" ;

            //获取预编译对象
            ps = conn.prepareStatement(sql1);
            //赋值
            ps.setInt(1,500);
            ps.setInt(2,3);

            //将sql2进行发送数据库,获取预编译对象
            ps2 = conn.prepareStatement(sql2);
            //赋值
            ps2.setInt(1,500) ;
            ps2.setInt(2,4);

            //通过预编译对象执行sql
            int count = ps.executeUpdate();

            //操作过程中,给一段代码(有问题的代码)
            //int i = 10 /0 ;//除数不能0  try中的某行代码一出问题,就执行catch语句,处理异常


            int count2 = ps2.executeUpdate();

            System.out.println(count+"---"+count2);

          //更新完上面的所有操作,正常提交数据
            conn.commit();
            System.out.println("转账成功");

        } catch (ArithmeticException throwables) {

            try {
                //回滚事务
                //void rollback() throws SQLException撤销在当前事务中所做的所有更改---
                conn.rollback();
                //回滚完之后,手动提交
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            throwables.printStackTrace(); //交个jvm处理  将异常信息打印控制台
            //System.out.println("出问题了...除数为0");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            //System.out.println("SQL语句出问题了");
        } finally {
            //释放资源
            JdbcUtils.close(ps,conn);
            JdbcUtils.close(ps2,conn);

        }
    }
}

JDBC综合实例

数据库建立学生表,建立idea项目利用Java连接数据库执行对数据库对象的增删查改.

1.建立项目与数据库与相关配置文件

建立数据库

CREATE DATABASE ccc;
USE ccc;
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT , 
	NAME VARCHAR(10),
	age INT,		   
	gender VARCHAR(3),		  
	address VARCHAR(20),
	birthday DATE
	);
	
INSERT INTO student(NAME,age,gender,address,birthday)
VALUES('库里',23,'男','夏洛特','1996-02-06'),
('汤普森',26,'男','洛杉矶','1997-02-23'),
('普尔',17,'男','明尼苏达','1991-02-08'),
('维金斯',35,'男','密尔沃基','1993-02-06'),
('鲁尼',48,'男','芝加哥','1996-02-09'),
('库明加',15,'男','旧金山','1999-11-06')
SELECT * FROM student ;

配置文件:

driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/ccc

username=root password=123456

initialSize=5

maxActive=10

maxWait=3000

2.导入相关jar包

3.建立学生类(pojo)

package com.qf.pojjo;

public class Student {
    private int id;
    private String name;
    protected  int age;
    private String gender;
    public String address;

    public Student() {
    }

    public int getId() {
        return id;
    }

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

    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 getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

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

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

4.建立Daoimpl项与DAO接口

package com.qf.dao;

import com.qf.pojjo.Student;

import java.sql.SQLException;
import java.util.List;

public interface StudentDao {
    //查询所有学生
    List<Student> findAll()throws SQLException;
    //模糊查询
    List<Student>findStudentByName(String name)throws  SQLException;
    //通过编号查询学生
    Student findStudentById(int id)throws SQLException;
    //添加学生
    int registerStudent(Student student)throws SQLException;
    //通过id删除学生
    void deleteStudent(int id)throws SQLException;

    //修改学生
    void updateStudent(Student student)throws SQLException;
}

package com.qf.dao.Impl;

import com.qf.dao.StudentDao;
import com.qf.pojjo.Student;
import com.qf.utils.JdbcUtils_WithDruid;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    //查询所有学生
    @Override
    public List<Student> findAll() throws SQLException {
        Connection conn = JdbcUtils_WithDruid.getConnection();
        String sql ="select * from student";
        PreparedStatement ps =conn.prepareStatement(sql);
        ResultSet rs =ps.executeQuery();
        List<Student>list =new ArrayList<>();
        Student s =null;
        while(rs.next()){
            s=new Student();
            s.setAge(rs.getInt("id"));
            s.setId(rs.getInt("id"));
            s.setGender(rs.getString("gender"));
            s.setName(rs.getString("name"));
            s.setAddress(rs.getString("address"));


            list.add(s);
        }
        JdbcUtils_WithDruid.close(rs,ps,conn);
        return list;
    }
    //模糊查询
    @Override
    public List<Student> findStudentByName(String name) throws SQLException {
        Connection conn = JdbcUtils_WithDruid.getConnection();
        String sql="select * from student where name like ?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,name);
        ResultSet rs = ps.executeQuery();
        List<Student>list =new ArrayList<>();
        Student s = null;
        while (rs.next()){
            s=new Student();
            s.setAddress(rs.getString("address"));
            s.setName(rs.getString("name"));
            s.setAge(rs.getInt("age"));
            s.setId(rs.getInt("id"));
            s.setGender(rs.getString("gender"));
            list.add(s);

        }
        JdbcUtils_WithDruid.close(rs,ps,conn);

        return list;
    }
        //通过编号查询学生实体
    @Override
    public Student findStudentById(int id) throws SQLException {
        Connection conn=JdbcUtils_WithDruid.getConnection();
        String sql = "select * from student where id=? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,id);
        Student s =null;
        ResultSet rs =ps.executeQuery();
        while (rs.next()){
            s=new Student();
            s.setGender(rs.getString("gender"));
            s.setAge(rs.getInt("age"));
            s.setId(rs.getInt("id"));
            s.setName(rs.getString("name"));
            s.setAddress(rs.getString("address"));
        }
        JdbcUtils_WithDruid.close(rs,ps,conn);
        return s;
    }
    //添加学生
    @Override
    public int registerStudent(Student student) throws SQLException {
        Connection conn=JdbcUtils_WithDruid.getConnection();
        String sql ="insert into student(name,age,gender,address) values(?,?,?,?)";
        PreparedStatement ps =conn.prepareStatement(sql);
        ps.setString(1,student.getName());
        ps.setInt(2,student.getAge());
        ps.setString(3,student.getGender());
        ps.setString(4,student.getAddress());

        int count =ps.executeUpdate();

        JdbcUtils_WithDruid.close(ps,conn);
        return count;



    }
    //删除员工
    @Override
    public void deleteStudent(int id) throws SQLException {
        Connection conn =JdbcUtils_WithDruid.getConnection();
        String sql ="delete from student where id=?";
        PreparedStatement ps =conn.prepareStatement(sql);
        ps.setInt(1,id);
        int count =ps.executeUpdate();
        System.out.println(count);
        JdbcUtils_WithDruid.close(ps,conn);


    }
    //修改学生
    @Override
    public void updateStudent(Student student) throws SQLException {
        Connection conn =JdbcUtils_WithDruid.getConnection();
        String sql ="update student set address =?,age=?,gender=?,name=? where id=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,student.getAddress());
        ps.setInt(2,student.getAge());
        ps.setString(3,student.getGender());
        ps.setString(4,student.getName());
        ps.setInt(5,student.getId());
        int count =ps.executeUpdate();
        System.out.println(count);
        JdbcUtils_WithDruid.close(ps,conn);


    }
}

5.创建Urils文件(德鲁伊)

package com.qf.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils_WithDruid {
    private static ThreadLocal<Connection> t1 =new ThreadLocal<>();
    private static DataSource ds;
    private JdbcUtils_WithDruid(){}
    static {
        try {
            Properties prop =new Properties();
            InputStream inputStream =JdbcUtils_WithDruid.class.getClassLoader().getResourceAsStream("druid.properties");
            prop.load(inputStream);
            System.out.println(prop);
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public static DataSource getDataSourse(){return ds;}
    public static Connection getConnection(){
        Connection conn =null;
        try {
            conn=t1.get();
            if(conn==null){
                Connection connection =ds.getConnection();
                t1.set(connection);
                return connection;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }



    public static void close(ResultSet rs, Statement stmt,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
                t1.remove();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    public static void close(Statement stmt,Connection conn){close(null,stmt,conn);}


    public static void main(String[] args){
        DataSource dataSource =JdbcUtils_WithDruid.getDataSourse();
        System.out.println(dataSource);

        Connection connection =JdbcUtils_WithDruid.getConnection();
        System.out.println(connection);
    }
}

6.创建测试类

package com.qf.test;

import com.qf.dao.Impl.StudentDaoImpl;
import com.qf.dao.StudentDao;
import com.qf.pojjo.Student;
import org.junit.Before;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;

public class StudentTest {
    private StudentDao sd;
    @Before
    public void init(){ sd=new StudentDaoImpl(); }
    //测试查询所有学生
    @Test
    public void testFindAll()throws SQLException{
        List<Student> students=sd.findAll();
        if (students!=null){
            for (Student s:students){
                System.out.println(s);
            }
        }

    }
    //测试模糊查询
    @Test
    public void testFindStudentByName() throws SQLException{
        List<Student> students=sd.findStudentByName("%库%");
        if(students!=null){
            for(Student s:students){
                System.out.println(s);
            }
        }
    }
    //测试根据id查询学生
    @Test
    public void testFindStudentById() throws SQLException{
        Student student =sd.findStudentById(1);
        if(student!=null){
            System.out.println(student);
        }
    }
    //测试添加学生
    @Test
    public void testAddStudent() throws SQLException{
        Student student = new Student();
        student.setName("迪温琴佐");
        student.setAddress("密尔沃基");
        student.setGender("男");
        student.setAge(26);
        sd.registerStudent(student);
    }
    //测试删除学生
    @Test
    public void testdeleteStudent() throws SQLException{
        sd.deleteStudent(5);
    }
    //测试修改学生
    @Test
    public void testUpdateStudent()throws  SQLException{
        Student s =new Student();
        s.setAge(11);
        s.setId(1);
        s.setAddress("旧金山");
        s.setName("库日天");
        s.setGender("女");
        sd.updateStudent(s);
    }
}