JDBC MySQL
JDBC (Java DataBase Connection) 是通过JAVA访问数据库 ,所以需要对数据库有基本的理解和应用。
步骤1:为项目导入mysql-jdbc的jar包
访问MySQL数据库需要用到第三方的类,这些第三方的类,都被压缩在一个叫做Jar的文件里。
为了代码能够使用第三方的类,需要为项目导入mysql的专用Jar包mysql-connector-java-5.0.8-bin.jar。导包步骤: 右键project->property->java build path->libaries->add external jars
步骤2:初始化驱动
通过Class.forName(“com.mysql.jdbc.Driver”);
初始化驱动类com.mysql.jdbc.Driver
就在 mysql-connector-java-5.0.8-bin.jar中
如果忘记了第一个步骤的导包,就会抛出ClassNotFoundException
Class.forName是把这个类加载到JVM中,加载的时候,就会执行其中的静态初始化块,完成驱动的初始化的相关工作。
package jdbc;
public class TestJDBC {
public static void main(String[] args) {
//初始化驱动
try {
//驱动类com.mysql.jdbc.Driver
//就在 mysql-connector-java-5.0.8-bin.jar中
//如果忘记了第一个步骤的导包,就会抛出ClassNotFoundException
Class.forName("com.mysql.jdbc.Driver");
System.out.println("数据库驱动加载成功 !");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
步骤3:建立与数据库的连接
建立与数据库的Connection连接,这里需要提供:
数据库所处于的ip:192.168.199.202(本机)
数据库的端口号: 3306(mysql专用端口号)
数据库名称 how2java
编码方式 UTF-8
账号 root
密码 admin
注: 这一步要成功执行,必须建立在mysql中有数据库how2java的基础上
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
// 建立与数据库的Connection连接
// 这里需要提供:
// 数据库所处于的ip:127.0.0.1 (本机)
// 数据库的端口号: 3306 (mysql专用端口号)
// 数据库名称 how2java
// 编码方式 UTF-8
// 账号 root
// 密码 admin
Connection c = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
System.out.println("连接成功,获取连接对象: " + c);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
步骤4:创建Statement
Statement是用于执行SQL语句的,比如增加,删除
步骤5:执行SQL语句
s.execute执行sql语句
执行成功后,用Mysql WorkBench进行查看,明确插入成功
执行SQL语句之前要确保数据库how2java中有表hero的存在,如果没有,需要事先创建表
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
// 准备sql语句
// 注意: 字符串要用单引号'
String sql = "insert into hero values(null,"+"'提莫'"+","+313.0f+","+50+")";
s.execute(sql);
System.out.println("执行插入语句成功");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
步骤6:关闭连接
数据库的连接是有限资源,相关操作结束后,养成关闭数据库的好习惯
先关闭Statement
后关闭Connection
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
Connection c = null;
Statement s = null;
try {
Class.forName("com.mysql.jdbc.Driver");
c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
"admin");
s = c.createStatement();
String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
s.execute(sql);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 数据库的连接时有限资源,相关操作结束后,养成关闭数据库的好习惯
// 先关闭Statement
if (s != null)
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 后关闭Connection
if (c != null)
try {
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
如果觉得上一步的关闭连接的方式很麻烦,可以参考关闭流 的方式,使用try-with-resource的方式自动关闭连接,因为Connection和Statement都实现了AutoCloseable接口
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
s.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
JDBC增删查改
CRUD是最常见的数据库操作,即增删改查
C 增加(Create)
R 读取查询(Retrieve)
U 更新(Update)
D 删除(Delete)
增,删,改示例:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";//Create
String sql2 = "delete from hero where id = 5";//Delete
String sql3 = "update hero set name = 'name 5' where id = 3";//Update
s.execute(sql1);
s.execute(sql2);
s.execute(sql3);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
查询语句:executeQuery 执行SQL查询语句
注意: 在取第二列的数据的时候,用的是rs.get(2) ,而不是get(1). 这个是整个Java自带的api里唯二的地方,使用基1的,即2就代表第二个。另一个地方是在PreparedStatement。
查询示例:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String args[]){
try {
Class.forName("com.mysql.jdbc.Driver");
}catch(ClassNotFoundException e) {
e.printStackTrace();
}
try (Connection c = DriverManager.getConnection("jdbc:mysql://192.168.199.202:3306/how2java?characterEncoding=UTF-8",
"root", "admin"); Statement s = c.createStatement();) {
String sql = "select * from hero";
// 执行查询语句,并把结果集返回给ResultSet
ResultSet rs = s.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");//可以使用字段名
String name = rs.getString(2);// 也可以使用字段的顺序
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
System.out.printf("%d\t%s\t%f\t%d%n",id, name, hp, damage);
}
// 不一定要在这里关闭ReultSet,因为Statement关闭的时候,会自动
//关闭ResultSet
// rs.close();
} catch (SQLException e) {
// TODO: handle exception
}
}
}
SQL语句判断账号密码是否正确
- 创建一个用户表,有字段name,password
- 插入一条数据
insert into user values(null,'dashen','thisispassword')
; - SQL语句判断账号密码是否正确
判断账号密码的正确方式是根据账号和密码到表中去找数据,如果有数据,就表明密码正确了,如果没数据,就表明密码错误。
不恰当的方式 是把uers表的数据全部查到内存中,挨个进行比较。 如果users表里有100万条数据呢? 内存都不够用的。
示例:
SQL语句:
CREATE TABLE user (
id int(11) AUTO_INCREMENT,
name varchar(30) ,
password varchar(30),
PRIMARY KEY (id)
) ;
insert into user values(null,'dashen','thisispassword');
TestJDBC.java:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
) {
String name = "dashen";
//正确的密码是:thisispassword
String password = "thisispassword1";
String sql = "select * from user where name = '" + name +"' and password = '" + password+"'";
// 执行查询语句,并把结果集返回给ResultSet
ResultSet rs = s.executeQuery(sql);
if(rs.next())
System.out.println("账号密码正确");
else
System.out.println("账号密码错误");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
JDBC预编译Statement
使用PreparedStatement
和 Statement一样,PreparedStatement也是用来执行sql语句的
与创建Statement不同的是,需要根据sql语句创建PreparedStatement
除此之外,还能够通过设置参数,指定相应的值,而不是Statement那样使用字符串拼接
注: 这是JAVA里唯二的基1的地方,另一个是查询语句中的ResultSet也是基1的。
示例:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "insert into hero values(null,?,?,?)";
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
// 根据sql语句创建PreparedStatement
PreparedStatement ps = c.prepareStatement(sql);
) {
// 设置参数
ps.setString(1, "提莫");
ps.setFloat(2, 313.0f);
ps.setInt(3, 50);
// 执行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
PreparedStatement的优点1-参数设置
Statement 需要进行字符串拼接,可读性和维护性比较差
String sql = “insert into hero values(null,”+"‘提莫’"+","+313.0f+","+50+")";
PreparedStatement 使用参数设置,可读性好,不易犯错
String sql = “insert into hero values(null,?,?,?)”;
PreparedStatement的优点2-性能表现
PreparedStatement有预编译机制,性能比Statement更快
PreparedStatement的优点3-防止SQL注入式攻击
假设name是用户提交来的数据
String name = “‘盖伦’ OR 1=1”;
使用Statement就需要进行字符串拼接
拼接出来的语句是:
select * from hero where name = ‘盖伦’ OR 1=1
因为有OR 1=1,这是恒成立的
那么就会把所有的英雄都查出来,而不只是盖伦
如果Hero表里的数据时海量的,比如几百万条,把这个表里的数据全部查出来
会让数据库负载变高,CPU100%,内存消耗光,响应变得极其缓慢
而PreparedStatement使用的是参数设置,就不会有这个问题
JDBC事务
不使用事务的情况
没有事务的前提下,假设业务操作是:加血,减血各做一次,结束后,英雄的血量不变
而减血的SQL不小心写错写成了 updata(而非update),那么最后结果是血量增加了,而非期望的不变。
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();) {
//没有事务的前提下
//假设业务操作时,加血,减血各做一次
//结束后,英雄的血量不变
//加血的SQL
String sql1 = "update hero set hp = hp +1 where id = 22";
s.execute(sql1);
//减血的SQL
//不小心写错写成了 updata(而非update)
String sql2 = "updata hero set hp = hp -1 where id = 22";
s.execute(sql2);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
使用事务
在事务中的多个操作,要么都成功,要么都失败
通过 c.setAutoCommit(false);关闭自动提交
使用 c.commit();进行手动提交
在22行-35行之间的数据库操作,就处于同一个事务当中,要么都成功,要么都失败
所以,虽然第一条SQL语句是可以执行的,但是第二条SQL语句有错误,其结果就是两条SQL语句都没有被提交。 除非两条SQL语句都是正确的。
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();) {
// 有事务的前提下
// 在事务中的多个操作,要么都成功,要么都失败
c.setAutoCommit(false);
// 加血的SQL
String sql1 = "update hero set hp = hp +1 where id = 22";
s.execute(sql1);
// 减血的SQL
// 不小心写错写成了 updata(而非update)
String sql2 = "updata hero set hp = hp -1 where id = 22";
s.execute(sql2);
// 手动提交
c.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
JDBC ORM & JDBC DAO
ORM=Object Relationship Database Mapping
对象和关系数据库的映射
简单说,一个对象,对应数据库里的一条记录
DAO=DataAccess Object
数据库访问对象
实际上就是把数据库相关的操作都封装在这个类里面,其他地方看不到JDBC的代码
示例:
DAO接口:
package jdbc;
import java.util.List;
import charactor.Hero;
public interface DAO{
//增加
public void add(Hero hero);
//修改
public void update(Hero hero);
//删除
public void delete(int id);
//获取
public Hero get(int id);
//查询
public List<Hero> list();
//分页查询
public List<Hero> list(int start, int count);
}
HeroDAO类,实现DAO接口:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import charactor.Hero;
public class HeroDAO implements DAO{
public HeroDAO() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
"admin");
}
public int getTotal() {
int total = 0;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select count(*) from hero";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("total:" + total);
} catch (SQLException e) {
e.printStackTrace();
}
return total;
}
public void add(Hero hero) {
String sql = "insert into hero values(null,?,?,?)";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, hero.name);
ps.setFloat(2, hero.hp);
ps.setInt(3, hero.damage);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
hero.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void update(Hero hero) {
String sql = "update hero set name= ?, hp = ? , damage = ? where id = ?";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setString(1, hero.name);
ps.setFloat(2, hero.hp);
ps.setInt(3, hero.damage);
ps.setInt(4, hero.id);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete(int id) {
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "delete from hero where id = " + id;
s.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public Hero get(int id) {
Hero hero = null;
try (Connection c = getConnection(); Statement s = c.createStatement();) {
String sql = "select * from hero where id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
hero = new Hero();
String name = rs.getString(2);
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
hero.name = name;
hero.hp = hp;
hero.damage = damage;
hero.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
return hero;
}
public List<Hero> list() {
return list(0, Short.MAX_VALUE);
}
public List<Hero> list(int start, int count) {
List<Hero> heros = new ArrayList<Hero>();
String sql = "select * from hero order by id desc limit ?,? ";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Hero hero = new Hero();
int id = rs.getInt(1);
String name = rs.getString(2);
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
hero.id = id;
hero.name = name;
hero.hp = hp;
hero.damage = damage;
heros.add(hero);
}
} catch (SQLException e) {
e.printStackTrace();
}
return heros;
}
}
JDBC数据库连接池
与线程池类似的,数据库也有一个数据库连接池。 不过他们的实现思路是不一样的。
本章节讲解了自定义数据库连接池类:ConnectionPool,虽然不是很完善和健壮,但是足以帮助大家理解ConnectionPool的基本原理。
数据库连接池原理-传统方式
当有多个线程,每个线程都需要连接数据库执行SQL语句的话,那么每个线程都会创建一个连接,并且在使用完毕后,关闭连接。
创建连接和关闭连接的过程也是比较消耗时间的,当多线程并发的时候,系统就会变得很卡顿。
同时,一个数据库同时支持的连接总数也是有限的,如果多线程并发量很大,那么数据库连接的总数就会被消耗光,后续线程发起的数据库连接就会失败。
数据库连接池原理-使用池
与传统方式不同,连接池在使用之前,就会创建好一定数量的连接。
如果有任何线程需要使用连接,那么就从连接池里面借用,而不是自己重新创建.
使用完毕后,又把这个连接归还给连接池供下一次或者其他线程使用。
倘若发生多线程并发情况,连接池里的连接被借用光了,那么其他线程就会临时等待,直到有连接被归还回来,再继续使用。
整个过程,这些连接都不会被关闭,而是不断的被循环使用,从而节约了启动和关闭连接的时间。
ConnectionPool构造方法和初始化
-
ConnectionPool() 构造方法约定了这个连接池一共有多少连接
-
在init() 初始化方法中,创建了size条连接。 注意,这里不能使用try-with-resource这种自动关闭连接的方式,因为连接恰恰需要保持不关闭状态,供后续循环使用
-
getConnection, 判断是否为空,如果是空的就wait等待,否则就借用一条连接出去
-
returnConnection, 在使用完毕后,归还这个连接到连接池,并且在归还完毕后,调用notifyAll,通知那些等待的线程,有新的连接可以借用了。
注:连接池设计用到了多线程的wait和notifyAll,这些内容可以在多线程交互部分学习。
package jdbc;
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> cs = new ArrayList<Connection>();
int size;
public ConnectionPool(int size) {
this.size = size;
init();
}
public void init() {
//这里恰恰不能使用try-with-resource的方式,因为这些连接都需要是"活"的,不要被自动关闭了
try {
Class.forName("com.mysql.jdbc.Driver");
for (int i = 0; i < size; i++) {
Connection c = DriverManager
.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");
cs.add(c);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public synchronized Connection getConnection() {
while (cs.isEmpty()) {
try {
this.wait();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
Connection c = cs.remove(0);
return c;
}
public synchronized void returnConnection(Connection c) {
cs.add(c);
this.notifyAll();
}
}
测试类
首先初始化一个有3条连接的数据库连接池
然后创建100个线程,每个线程都会从连接池中借用连接,并且在借用之后,归还连接。 拿到连接之后,执行一个耗时1秒的SQL语句。
运行程序,就可以观察到如图所示的效果
package jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import jdbc.ConnectionPool;
public class TestConnectionPool {
public static void main(String[] args) {
ConnectionPool cp = new ConnectionPool(3);
for (int i = 0; i < 100; i++) {
new WorkingThread("working thread" + i, cp).start();
}
}
}
class WorkingThread extends Thread {
private ConnectionPool cp;
public WorkingThread(String name, ConnectionPool cp) {
super(name);
this.cp = cp;
}
public void run() {
Connection c = cp.getConnection();
System.out.println(this.getName()+ ":\t 获取了一根连接,并开始工作" );
try (Statement st = c.createStatement()){
//模拟时耗1秒的数据库SQL语句
Thread.sleep(1000);
st.execute("select * from hero");
} catch (SQLException | InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
cp.returnConnection(c);
}
}