文章目录
十一、JDBC数据库访问
JDBC准备工作
为项目导入mysql-jdbc的jar包
访问MySQL数据库需要用到第三方的类,这些第三方的类,都被压缩在一个叫做Jar的文件里
mysql-connector-java-5.0.8-bin.jar
idea导入jar包方式:File—>Project Structure—>Modules—>Dependencies—>“+”—>JARs or Directories
初始化驱动
通过Class.forName(“com.mysql.jdbc.Driver”);初始化
Class.forName是把这个类加载到JVM中,加载的时候,就会执行其中的静态初始化块。
建立与数据库连接
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/myjava?characterEncoding=UTF-8";
String username = "root";
String password = "admin";
Connection c = DriverManager.getConnection(url,username,password);
创建Statement
Statement是用于执行SQL语句的,Statement s = c.createStatement();
执行SQL语句
String sql = "insert into hero values(字段1,字段2,字段3,字段4)";
s.execute(sql);
关闭连接
数据库的连接是有限资源,相关操作结束后,养成关闭数据库的好习惯,先关闭Statement,后关闭Connection
使用try-with-resource的方式自动关闭连接
try(Connection对象和Statament对象){
执行sql语句
}catch (SQLException e) {
e.printStackTrace();
}
JDBC的增删改
//增加数据
String sql = "insert into person values(null,'山鸡',12,1300,500)";
//删除数据
String sql2 = "delete from person where id = 6";
//改变数据
String sql3 = "update person set name = '阿七' where id = 7";
JDBC查询
查询语句
executeQuery 执行SQL查询语句
String sql4 = "select * from person";
ResultSet rs = statement.executeQuery(sql4);
while (rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
float salary = rs.getInt(4);
int banlance = rs.getInt(5);
System.out.printf("%d\t%s\t%d\t%f\t%d%n", id, name, age, salary,banlance);
}
查询指定是否存在的语句
String sql5 = "select * from person where name = '老八' and age = '12'";
ResultSet rs = statement.executeQuery(sql5);
if(rs.next()){
System.out.println("存在");
}else {
System.out.println("不存在");
}
查询获取总数
String sql6 = "select count(*) from person";
ResultSet rs = statement.executeQuery(sql6);
int total = 0;
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("表person中共有:"+total+"条数据");
预编译Statement
使用PreparedStatement
和 Statement一样,PreparedStatement也是用来执行sql语句的
与创建Statement不同的是,需要根据sql语句创建PreparedStatement
除此之外,还能够通过设置参数,指定相应的值,而不是Statement那样使用字符串拼接
String sql = "insert into person values(null,?,?,?,?)";
PreparedStatement ps = c.prepareStatement(sql);
// 设置参数
ps.setString(1, "泉水");
ps.setInt(2, 50);
ps.setFloat(3, 5000);
ps.setInt(4, 25000);
// 执行
ps.execute();
PreparedStatement的优点
1、使用参数设置,可读性好,不易犯错
2、PreparedStatement有预编译机制,性能比Statement更快
3、防止SQL注入式攻击
execute与executeUpdate的区别
相同点
execute与executeUpdate的相同点:都可以执行增加,删除,修改
不同点
1、execute可以执行查询语句,后通过getResultSet,把结果集取出来,executeUpdate不能执行查询语句
2、execute返回boolean类型,true表示执行的是查询语句,false表示执行的是insert,delete,update等等,
executeUpdate返回的是int,表示有多少条数据受到了影响
特殊操作
获取自增长id
// 在执行完插入语句后,MySQL会为新插入的数据分配一个自增长id
// JDBC通过getGeneratedKeys获取该id
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
System.out.println(id);
}
获取表的元数据
元数据概念:和数据库服务器相关的数据,比如数据库版本,有哪些表,表有哪些字段,字段类型是什么等等。
DatabaseMetaData dbmd = c.getMetaData();
// 获取数据库服务器产品名称
System.out.println("数据库产品名称:\t"+dbmd.getDatabaseProductName());
// 获取数据库服务器产品版本号
System.out.println("数据库产品版本:\t"+dbmd.getDatabaseProductVersion());
// 获取数据库服务器用作类别和表名之间的分隔符 如test.user
System.out.println("数据库和表分隔符:\t"+dbmd.getCatalogSeparator());
// 获取驱动版本
System.out.println("驱动版本:\t"+dbmd.getDriverVersion());
System.out.println("可用的数据库列表:");
// 获取数据库名称
ResultSet rs = dbmd.getCatalogs();
while (rs.next()) {
System.out.println("数据库名称:\t"+rs.getString(1));
}
事务
没有使用事务
在没有使用事务时,在对数据进行增加后再减少时,期望是保持不变,但是如果减少的sql语句写错,结果就不是期望的结果
使用事务
在事务中的多个操作,要么都成功,要么都失败
通过 c.setAutoCommit(false);关闭自动提交
使用 c.commit();进行手动提交
c.setAutoCommit(false);
// 增加的SQL
String sql1 = "update person set age = age +1 where id = 22";
s.execute(sql1);
// 减少的SQL
// 不小心写错写成了 updata(而非update)
String sql2 = "updata person set age = age -1 where id = 22";
s.execute(sql2);
// 手动提交
c.commit();
MYSQL 表的类型必须是INNODB才支持事务
在Mysql中,只有当表的类型是INNODB的时候,才支持事务,所以需要把表的类型设置为INNODB,否则无法观察到事务.
//修改表的类型为INNODB的SQL:
alter table hero ENGINE = innodb;
//查看表的类型的SQL
show table status from myjava;
String sql = "show table status from myjava;";
ResultSet rs = statement.executeQuery(sql);
if(rs.next()){
String str = rs.getString(2);
System.out.println(str);
}
ORM
ORM=Object Relationship Database Mapping(对象和关系数据库的映射)
简单说,一个对象,对应数据库里的一条记录
DAO
DAO=DataAccess Object(数据访问对象)
实际上就是运用了练习-ORM中的思路,把数据库相关的操作都封装在这个类里面,其他地方看不到JDBC的代码
数据库连接池
数据库连接池原理-传统方式
当有多个线程,每个线程都需要连接数据库执行SQL语句的话,那么每个线程都会创建一个连接,并且在使用完毕后,关闭连接。
创建连接和关闭连接的过程也是比较消耗时间的,当多线程并发的时候,系统就会变得很卡顿。
同时,一个数据库同时支持的连接总数也是有限的,如果多线程并发量很大,那么数据库连接的总数就会被消耗光,后续线程发起的数据库连接就会失败
数据库连接池原理-使用池
与传统方式不同,连接池在使用之前,就会创建好一定数量的连接。如果有任何线程需要使用连接,那么就从连接池里面借用,而不是自己重新创建.使用完毕后,又把这个连接归还给连接池供下一次或者其他线程使用。倘若发生多线程并发情况,连接池里的连接被借用光了,那么其他线程就会临时等待,直到有连接被归还回来,再继续使用。整个过程,这些连接都不会被关闭,而是不断的被循环使用,从而节约了启动和关闭连接的时间。
ConnectionPool构造方法和初始化
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ConnectionPool {
List<Connection> list = new ArrayList<Connection>();
int size;
public ConnectionPool(int size) {
this.size = size;
init();
}
public void init(){
String url = "jdbc:mysql://127.0.0.1:3306/myjava?characterEncoding=UTF-8";
String username = "root";
String password = "admin";
try {
Class.forName("com.mysql.jdbc.Driver");
for (int i = 0; i < size; i++) {
Connection c = DriverManager.getConnection(url,username,password);
list.add(c);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public synchronized Connection getConnection(){
while (list.isEmpty()){
try {
this.wait();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
return list.remove(0);
}
public synchronized void returnConnection(Connection c){
list.add(c);
this.notifyAll();
}
}
连接池测试类
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class Txt {
public static void main(String[] args) {
ConnectionPool cp = new ConnectionPool(3);
for (int i = 0; i < 100; i++) {
new SqlThread("线程"+i,cp).start();
}
}
}
class SqlThread extends Thread{
public ConnectionPool cp;
public String name;
public SqlThread(String name,ConnectionPool cp) {
super(name);
this.cp = cp;
}
@Override
public void run() {
Connection c = cp.getConnection();
System.out.println(this.getName()+ ":\t 获取了一根连接,并开始工作" );
try(Statement statement = c.createStatement()) {
Thread.sleep(2000);
statement.execute("select * from person");
} catch (SQLException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
cp.returnConnection(c);
}
}
练习
1)练习-一次性 插入100条数据
借助循环,和上面学习到的JDBC的知识,向数据库中插入100条数据,并在mysql-front中观察查询结果
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Exercise {
public static void main(String[] args){
//?characterEncoding=UTF-8加上编码集,不然数据库里中文乱码
String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
String username = "root";
String password = "admin";
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try(Connection c = DriverManager.getConnection(url,username,password);
Statement statement = c.createStatement();){
for (int i = 0; i < 100; i++) {
int age =(int)(Math.random()*99+1);
statement.execute("insert into info values(null,'名称"+i+"',"+age+")");
}
System.out.println("写入完成!");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
2)练习-增 删 改
设计一个方法public static void execute(String sql)
方法接受的参数是SQL语句,无论是增加,删除,还是修改,都调用这个方法,每次传不同的SQL语句作为参数
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Exercise {
public static String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
public static String username = "root";
public static String password = "admin";
public static void main(String[] args){
//增
// String sql = "insert into info values(null,'名称2',57)";
//删
// String sql = "delete from info where id = 3";
//改
String sql = "update info set age = 88 where id = 1";
execute(sql);
}
public static void execute(String sql){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try(Connection c = DriverManager.getConnection(url,username,password);
Statement statement = c.createStatement();){
statement.execute(sql);
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
3)练习-分页查询
设计一个方法,进行分页查询public static void list(int start, int count)
start 表示开始页数,count表示一页显示的总数
import java.sql.*;
public class Exercise {
public static String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
public static String username = "root";
public static String password = "admin";
public static void main(String[] args){
list(11,10);
}
public static void list(int start, int count){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try(Connection c = DriverManager.getConnection(url,username,password);
Statement statement = c.createStatement();){
String sql = "select * from info limit " + (start-1) + "," + count;
ResultSet rs = statement.executeQuery(sql);
while (rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.printf("%d,%s,%d\n",id,name,age);
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
4)练习-Statement性能比较
向数据库中插入10000条数据,分别使用Statement和PreparedStatement,比较各自花的时间差异
import java.sql.*;
public class Exercise {
public static void main(String[] args){
//?characterEncoding=UTF-8加上编码集,不然数据库里中文乱码
String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
String username = "root";
String password = "admin";
long startTime = System.currentTimeMillis();
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "insert into info values(null,?,?)";
try(Connection c = DriverManager.getConnection(url,username,password);
Statement statement = c.createStatement();
PreparedStatement ps = c.prepareStatement(sql)){
for (int i = 0; i < 2000; i++) {
int age =(int)(Math.random()*99+1);
statement.execute("insert into info values(null,'名称"+i+"',"+age+")");
// ps.setString(1,"名称"+i);
// ps.setInt(2,age);
// ps.execute();
}
System.out.println("写入完成!");
} catch (SQLException ex) {
ex.printStackTrace();
}
long endTime = System.currentTimeMillis();
System.out.println("花费时间:"+(endTime-startTime)+"ms");
}
}
5)练习-分页查询
使用execute方式,而不是executeQuery方式完成练习
import java.sql.*;
public class Exercise {
public static String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
public static String username = "root";
public static String password = "admin";
public static void main(String[] args){
list(11,10);
}
public static void list(int start, int count){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try(Connection c = DriverManager.getConnection(url,username,password);
Statement statement = c.createStatement();){
String sql = "select * from info limit " + (start-1) + "," + count;
statement.execute(sql);
ResultSet rs = statement.getResultSet();
while (rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.printf("%d,%s,%d\n",id,name,age);
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
6)练习-自增长id
当插入一条数据之后,通过获取自增长id,得到这条数据的id,比如说是55
删除这条数据的前一条,54.
如果54不存在,则删除53,以此类推直到删除上一条数据。
import java.sql.*;
public class Exercise {
public static String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
public static String username = "root";
public static String password = "admin";
public static void main(String[] args){
exe();
}
public static void exe(){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "insert into info values(null,?,?)";
int id = 0;
try(Connection c = DriverManager.getConnection(url,username,password);
PreparedStatement ps = c.prepareStatement(sql);){
ps.setString(1,"姓名");
ps.setInt(2,55);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
//获得插入数据的id
if(rs.next()){
id = rs.getInt(1);
System.out.println(id);
}
//将id-1的位置数据删除,删除不成功id会减一,继续删除
while (ps.executeUpdate("delete from info where id ="+(id-1)) == 0) {
id--;
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
7)练习-事务
根据,当c.setAutoCommit(false);时,事务是不会提交的。只有执行使用 c.commit(); 才会提交进行
设计一个代码,删除表中前10条数据,但是删除前会在控制台弹出一个提示:
是否要删除数据(Y/N)
import java.sql.*;
import java.util.Scanner;
public class Exercise {
public static String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
public static String username = "root";
public static String password = "admin";
public static void main(String[] args){
exe();
}
public static void exe(){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "select * from info limit 0,3";
String sql2 = "delete from info where id = ?";
int id = 0;
ResultSet rs = null;
try(Connection c = DriverManager.getConnection(url,username,password);
PreparedStatement ps = c.prepareStatement(sql);
PreparedStatement ps2 = c.prepareStatement(sql2)){
c.setAutoCommit(false);//取消自动提交
ps.execute();//执行查询指令
rs = ps.getResultSet();//得到查询结果
while (rs.next()){
id = rs.getInt(1);
ps2.setInt(1,id);
ps2.execute();
}
System.out.println("请选择是否删除Y/N?");
Scanner sc = new Scanner(System.in);
String str = sc.nextLine();
if(str.equals("Y")){
c.commit();
System.out.println("已经删除");
}else if(str.equals("N")) {
System.out.println("已放弃删除");
}else {
System.out.println("放弃选择的权力");
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
8)练习-ORM
根据ORM的思想,设计其他几个常见的ORM方法:
把一个Person对象插入到数据库中public static void add(Person p)
把这个Person对象对应的数据删除掉public static void delete(Person p)
更新这条Person对象public static void update(Person p)
把所有的Person数据查询出来,转换为Person对象后,放在一个集合中返回public static List<Person> list();
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class Exercise {
public static String url = "jdbc:mysql://127.0.0.1:3306/person?characterEncoding=UTF-8";
public static String username = "root";
public static String password = "admin";
public static void main(String[] args){
Person p = new Person("杜甫",91);
Person p1 = new Person("李白",88);
Person p2 = new Person("李白",98);
diver();
// add(p);
// delete(p1);
// update(p2);
List list = list();
System.out.println(list.size());
}
public static void diver(){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//把一个Person对象插入到数据库中
public static void add(Person p){
String sql = "insert into info values(null,?,?)";
try(Connection c = DriverManager.getConnection(url,username,password);
PreparedStatement ps = c.prepareStatement(sql)){
ps.setString(1,p.name);
ps.setInt(2,p.age);
ps.execute();
System.out.println("插入数据成功!");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
//把这个Person对象对应的数据删除掉
public static void delete(Person p){
String sql = "delete from info where name = ? and age = ?";
try(Connection c = DriverManager.getConnection(url,username,password);
PreparedStatement ps = c.prepareStatement(sql)){
ps.setString(1,p.name);
ps.setInt(2,p.age);
ps.execute();
System.out.println("删除数据成功!");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
//更新这条Person对象
public static void update(Person p){
String sql = "update info set age = ? where name = ?";
try(Connection c = DriverManager.getConnection(url,username,password);
PreparedStatement ps = c.prepareStatement(sql)){
ps.setInt(1,p.age);
ps.setString(2,p.name);
ps.execute();
System.out.println("升级数据成功!");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
//查询Person对象
public static List<Person> list(){
String sql = "select * from info";
List<Person> list = new ArrayList<>();
try(Connection c = DriverManager.getConnection(url,username,password);
PreparedStatement ps = c.prepareStatement(sql)){
ps.execute();
ResultSet rs = ps.getResultSet();
while (rs.next()){
String name = rs.getString(2);
int age = rs.getInt(3);
list.add(new Person(name,age));
}
System.out.println("升级数据成功!");
} catch (SQLException ex) {
ex.printStackTrace();
}
return list;
}
}
9)练习-数据库连接池
向数据库中插入100条数据,比较传统方式和数据库连接池方式的性能差异
1、使用传统方式创建100个线程,每个线程都会创建新的连接,通过这个连接向数据库插入1条数据,然后关闭这个连接
2、使用数据库连接池的方式,创建一个有10条连接的连接池,然后创建100个线程,每个线程都会向连接池借用连接,借用到后,向数据库插入1条数据,然后归还这个连接
通过时间统计,比较这两种方式的性能表现差异。
数据池插入数据花费时间:10937ms
传统方式插入数据花费时间:1772ms
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
public class Exercise {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8";
String username = "root";
String password = "admin";
ConnectionPool cp = new ConnectionPool(10);
List<Thread> list = new ArrayList<>();//存储线程
Long startTime = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
Thread t = new SqlThread("池线程"+i,cp);
list.add(t);
t.start();
}
for (Thread t : list) {
try {
t.join();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
Long endTime = System.currentTimeMillis();
System.out.println("数据池插入数据花费时间:"+(endTime-startTime)+"ms");
Long startTime1 = System.currentTimeMillis();
List<Thread> list2 = new ArrayList<>();//存储线程
for (int i = 0; i <100 ; i++) {
Thread t1 = new legacyThread("传统线程"+i);
list2.add(t1);
t1.start();
}
for (Thread t1 : list2) {
try {
t1.join();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
Long endTime1 = System.currentTimeMillis();
System.out.println("传统方式插入数据花费时间:"+(endTime1-startTime1)+"ms");
}
}
class legacyThread extends Thread{
public static String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8";
public static String username = "root";
public static String password = "admin";
public String name;
public legacyThread(String name) {
super(name);
}
@Override
public void run() {
String sql = "insert into demo values(null,?,?)";
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try(Connection c = DriverManager.getConnection(url,username,password);
PreparedStatement ps = c.prepareStatement(sql)){
ps.setString(1,"传统");
ps.setInt(2,55);
Thread.sleep(1000);
ps.execute();
} catch (SQLException ex) {
ex.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
class SqlThread extends Thread{
public ConnectionPool cp;
public String name;
public SqlThread(String name,ConnectionPool cp) {
super(name);
this.cp = cp;
}
@Override
public void run() {
String sql = "insert into demo values(null,?,?)";
Connection c = cp.getConnection();
try(PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1,"线程池");
ps.setInt(2,66);
Thread.sleep(1000);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
cp.returnConnection(c);
}
}