JDBC学习汇总

概念

JDBC：JDBC是Java提供的一套用来操作数据库的接口

通过Java代码操作数据库

1.确定数据库是可以正常使用（MySQL服务是否正常开启）

2.确定MySQL的账号和密码是正确的

3.确定MySQL版本和MySQL驱动版本匹配

4.在工程（module）上右键创建一个目录并将驱动包放在此目录中

5.在jar包上右键-->add as library

 获取Connection方式一

connect(String url,java.util.Properties info)
         url: mysql的连接地址
         jdbc:mysql://localhost:3306/atguigu
         jdbc:mysql:协议
        localhost:mysql服务器的地址
        3306：端口号
        atguigu：库的名字

  @Testpublic void test() throws SQLException {//1.创建Driver对象Driver driver = new com.mysql.jdbc.Driver();//全类名：包含包名在内的类的全名称//2.调用方法--获取Connection对象(有了该对象才能操作数据库)String s = "jdbc:mysql://localhost:3306/myemployees";Properties p = new Properties();p.setProperty("user","root");//账号，key不能随便写p.setProperty("password","123123");//密码，key不能随便写Connection connect = driver.connect(s,p);System.out.println(connect);}

Properties

1.Properties是Hashtable的子类

2.Properties中的key，value默认是String类型

3.常用Properties读取配置文件

//首先在项目中创建一个文件，文件名为jdbc.properties
//文件内容如下：
user=root
password=123321//程序如下：@Testpublic void test() throws IOException {//1.创建Properties对象Properties p = new Properties();//2.创建流FileInputStream fis = new FileInputStream("jdbc.properties");//3.加载流--将流加载到Properties中p.load(fis);//4.通过Properties读取文件中的内容String user = p.getProperty("user");String password = p.getProperty("password");System.out.println(user + "-----" + password);//5.关闭资源fis.close();}

获取Connection方式二：通过DriverManager

    @Testpublic void test2() throws SQLException {//1.创建Driver对象Driver driver = new com.mysql.jdbc.Driver();//2.将driver注册到DriverManager中DriverManager.registerDriver(driver);//获取Connection对象String url = "jdbc:mysql://localhost:3306/myemployees";Connection connection = DriverManager.getConnection(url,"root","123123");System.out.println(connection);}

方式二的优化

    @Testpublic void test3() throws ClassNotFoundException, SQLException {//1.让driver类中的静态代码块执行Class.forName("com.mysql.jdbc.Driver");//2.获取connection对象String url = "jdbc:mysql://localhost:3306/myemployees";Connection connection = DriverManager.getConnection(url,"root","123123");System.out.println(connection);}

获取Connection方式三（最终方式）

//首先在项目中创建一个文件，文件名为jdbc.properties
//文件内容如下：
user=root
password=123321//程序如下：@Testpublic void test4() throws ClassNotFoundException, SQLException, IOException {String className = "";String url = "";String user = "";String password = "";//读取配置文件//1.创建Properties对象Properties p = new Properties();//2.创建流FileInputStream fis = new FileInputStream("jdbc.properties");//3.加载流--将流加载到Properties中p.load(fis);//4.通过Properties读取文件中的内容user = p.getProperty("user");password = p.getProperty("password");url = p.getProperty("url");className = p.getProperty("className");System.out.println(user + "---" + password + "---" + url + "---" + className);//5.关闭资源fis.close();//1.让driver类中的静态代码块执行Class.forName(className);//2.获取connection对象Connection connection = DriverManager.getConnection(url,user,password);System.out.println(connection);}

JDBCUtils工具类

//首先在项目中创建一个文件，文件名为jdbc.properties
//文件内容如下：
user=root
password=123321
url=jdbc:mysql://localhost:3306/myemployees
className=com.mysql.jdbc.Driver//----------------------------------
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;/*
* 工具类
* */
public class JDBCUtils {private static String className;private static String url;private static String user;private static String password;static {FileInputStream fis = null;try {//读取配置文件//1.创建Properties对象Properties p = new Properties();//2.创建流fis = new FileInputStream("jdbc.properties");//3.加载流--将流加载到Properties中p.load(fis);//4.通过Properties读取文件中的内容user = p.getProperty("user");password = p.getProperty("password");url = p.getProperty("url");className = p.getProperty("className");System.out.println(user + "---" + password + "---" + url + "---" + className);}catch (Exception e){e.printStackTrace();//打印异常信息//将编译时异常转为运行时异常---终止程序的运行throw new RuntimeException(e.getMessage());//e.getMessage():获取异常信息}finally {if (fis != null) {//5.关闭资源try {fis.close();} catch (IOException e) {throw new RuntimeException(e);}}}}//获取Connection对象public static Connection getConnection(){try {//1.让driver类中的静态代码块执行Class.forName(className);//2.获取connection对象Connection connection = DriverManager.getConnection(url,user,password);
//        System.out.println(connection);return connection;}catch (Exception e){e.printStackTrace();throw new RuntimeException(e.getMessage());}}//关闭资源public static void close(Connection connection, PreparedStatement ps) {if (connection != null){try {connection.close();} catch (SQLException e) {e.printStackTrace();}}if (ps != null){try {ps.close();} catch (SQLException e) {e.printStackTrace();}}}
}

向表中插入数据

    @Testpublic void test() throws SQLException {//1.获取Connection对象Connection connection = JDBCUtils.getConnection();//2.sql语句//?:占位符String sql = "insert into student(id,name,sid) values(?,?,?)";//3.对SQL预编译//调用PrepareStatement返回PrepareStatement对象，有了该对象就可以给占位符赋值PreparedStatement ps = connection.prepareStatement(sql);//4.给占位符赋值/** setInt(int parameterIndex,int x)* parameterIndex:第几个占位符* */ps.setInt(1,10);ps.setString(2,"longge");ps.setInt(3,1000);//5.执行sql语句int result = ps.executeUpdate();//executeUpdate:只是用来执行增，删，改System.out.println("共有" + result + "行数据受到影响");//6.关闭资源JDBCUtils.close(connection,ps);}

更改表中数据

     /** 修改数据库中数据* */@Testpublic void test1() throws SQLException {//1.获取Connection对象Connection connection = JDBCUtils.getConnection();//2.sql语句String sql = "update student set id=? where name=?";//3.预编译PreparedStatement ps = connection.prepareStatement(sql);//3.1给占位符赋值ps.setInt(1,9);ps.setString(2,"longge");//3.2执行sql语句ps.executeUpdate();//4.关闭资源JDBCUtils.close(connection,ps);}

删除表中数据

    /** 删除数据库中数据* */@Testpublic void test2() throws SQLException {//1.获取Connection对象Connection connection = JDBCUtils.getConnection();//2.sql语句String sql = "delete from student where id = ?";//3.预编译PreparedStatement ps = connection.prepareStatement(sql);//3.1给占位符赋值ps.setInt(1,9);//3.2执行sql语句ps.executeUpdate();//4.关闭资源JDBCUtils.close(connection,ps);}

查询表中的一条数据

    /** 查询表中的一条数据* */@Testpublic void test() throws SQLException {//1.获取Connection对象Connection connection = JDBCUtils.getConnection();//2.sql语句String sql = "select id,name,sid from student where id = ?";//3.预编译PreparedStatement ps = connection.prepareStatement(sql);//4.给占位符赋值ps.setInt(1,3);//5.执行sql语句ResultSet rs = ps.executeQuery();//executeQuery():执行查询的语句//6.通过ResultSet遍历数据while (rs.next()){//next():如果有数据结果为true//7.获取对应的字段中的数据//getInt(String columnLabel):通过字段的名字获取对应的值int id = rs.getInt("id");String name = rs.getString("name");int sid = rs.getInt("sid");System.out.println(id + "=" + name + "=" + sid);}//8.关闭资源JDBCUtils.close(connection,ps,rs);}

查询表中所有数据（查询一条数据的修改式）

    /** 查询表中的所有数据* */@Testpublic void test2() throws SQLException {//1.获取Connection对象Connection connection = JDBCUtils.getConnection();//2.sql语句String sql = "select id,name,sid from student";//3.预编译PreparedStatement ps = connection.prepareStatement(sql);//5.执行sql语句ResultSet rs = ps.executeQuery();//executeQuery():执行查询的语句//6.通过ResultSet遍历数据while (rs.next()){//next():如果有数据结果为true//7.获取对应的字段中的数据//getInt(String columnLabel):通过字段的名字获取对应的值int id = rs.getInt("id");String name = rs.getString("name");int sid = rs.getInt("sid");System.out.println(id + "=" + name + "=" + sid);}//8.关闭资源JDBCUtils.close(connection,ps,rs);}

查询表中的所有数据（调用类的方法）

    @Testpublic void test3() throws SQLException {List<Student> students = getStudents();for (Student student : students) {System.out.println(student);}}/** 自定义一个方法。调用此方法就可以获取表中所有的数据* */public List<Student> getStudents() throws SQLException {//创建一个集合用来存放对象List<Student> list = new ArrayList<>();Connection connection = JDBCUtils.getConnection();String sql = "select id,name,sid from student";PreparedStatement ps = connection.prepareStatement(sql);ResultSet rs = ps.executeQuery();while (rs.next()){//next():如果有数据结果为trueint id = rs.getInt("id");String name = rs.getString("name");int sid = rs.getInt("sid");//封装Student s = new Student(id,name,sid);//将对象放入到集合中list.add(s);}//8.关闭资源JDBCUtils.close(connection,ps,rs);//返回集合return list;}

事务

import com.atguigu.jdbc2.JDBCUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;/*#事务：#一组逻辑操作单元，使数据从一种状态变换到另一种状态#案例：# AA给CC转账1000元try{事务开启AA减去1000元的操作；System.out.println(1/0);CC加上1000元的操作;事务提交}catch(Exception e){事务回滚;}finally{允许事务提交}遇到的问题：可能会发生AA的操作成功但是CC的操作失败解决思路：将AA和CC的操作看成一个整体看成一个整体要么都成功要么都失败CREATE TABLE account(NAME VARCHAR(20),balance INT);
* */
public class Account {public static void main(String[] args) {//获取Connection对象Connection connection = JDBCUtils.getConnection();PreparedStatement ps = null;try {//============开启事务---禁止自动提交===============connection.setAutoCommit(false);//sql语句String sql = "update account set balance=? where name=?";//预编译ps = connection.prepareStatement(sql);//给占位符赋值//AA减去1000ps.setInt(1, 1000);ps.setString(2, "aa");//执行sql语句ps.executeUpdate();
//            System.out.println(1 / 0);//CC加上1000ps.setInt(1, 3000);ps.setString(2, "cc");//执行sql语句ps.executeUpdate();//=========事务---提交=====connection.commit();} catch (Exception e) {//======事务---回滚====try {connection.rollback();} catch (SQLException ex) {throw new RuntimeException(ex);}e.printStackTrace();} finally {//允许事务提交try {connection.commit();} catch (SQLException e) {e.printStackTrace();}//关闭资源JDBCUtils.close(connection, ps);}}
}

数据库连接池

package com.atguigu.jdbc3;import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.jupiter.api.Test;import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;/*
* 数据库连接池：Druid
* */
public class DruidDemo {/** 方式一：* */@Testpublic void test() throws SQLException {//1.创建数据库连接池对象DruidDataSource dataSource = new DruidDataSource();//2.给属性赋值dataSource.setUsername("root");//mysql账号dataSource.setPassword("123123");//mysql密码dataSource.setDriverClassName("com.mysql.jdbc.Driver");//Driver类的全类名dataSource.setUrl("jdbc:mysql://localhost:3306/myemployees");//3.获取Connection对象Connection connection = dataSource.getConnection();System.out.println(connection);//4.关闭资源connection.close();}/** 方式二：* */@Testpublic void test2() throws Exception {Properties p = new Properties();FileInputStream fis = new FileInputStream("druid.properties");p.load(fis);//加载流//1.创建数据库的连接对象DataSource dataSource = DruidDataSourceFactory.createDataSource(p);//2.获取数据库连接对象Connection connection = dataSource.getConnection();System.out.println(connection);//3.关闭connection.close();}
}

不能插入中文问题

url=jdbc:mysql://localhost:3306/myemployees?characterEncoding=utf8