MySQL学习(四)
JDBC
1、数据库驱动
驱动:声卡、显卡、数据库
我们的程序会通过数据库厂商的数据库驱动和数据库打交道!
2、JDBC简介
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC
这些规范的实现有具体的厂商去做
对于开发人员来说,我们只需要掌握JDBC接口的操作即可!
需要用到java.sql和javax.sql包,这两个包JDK自带;
还需要导入一个数据库驱动包:mysql-connector-java-5.1.47.jar
3、第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
1、创建一个普通项目
2、导入数据库驱动
创建lib,将jar包复制进去,右键,选中ADD as Library,OK
3、编写测试代码
首先给数据库中存入数据
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
import java.sql.*;
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
//2、用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String name = "root";
String password = "123456";
//3、连接成功,返回数据库对象
Connection connection = DriverManager.getConnection(url, name, password);
//4、获取执行SQL的对象 Statement:是执行SQL的对象
Statement statement = connection.createStatement();
//5、执行SQL的对象 去 执行SQL,可能存在结果,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回结果集,结果集中封装了我们全部的查询出来的结果
while(resultSet.next()){
System.out.println("id = "+resultSet.getObject("id"));
System.out.println("name = "+resultSet.getObject("name"));
System.out.println("password = "+resultSet.getObject("password"));
System.out.println("email = "+resultSet.getObject("email"));
System.out.println("birthday = "+resultSet.getObject("birthday"));
System.out.println("================================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1、加载驱动
2、连接数据库DiverManger
3、获得之心sql的对象Statement
4、获得返回的结果集
5、释放连接
DiverManger
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, name, password);
//conection 代表数据库
//数据库设置自动提交
//事务提交 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql -- 3306
//协议://主机地址:端口号/数据库?参数1&参数2&参数3
//oralce -- 1521
//jdbc:orcale:thin:@localhost:1521:sid
Statement 执行SQL的对象 PrepareStatement执行SQL的对象
String sql = "SELECT * FROM users";//编写SQL
statement.executeQuery();//查询操作返回ResultSet
statement.execute();//执行如何SQL
statement.executeUpdate();//更新、插入、删除、都是用这个,返回一个受影响的行数
ResultSet查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
遍历,指针
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(); //移动到指定行
释放资源
//耗资源,用完关掉
resultSet.close();
statement.close();
connection.close();
4、statement对象
jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象先该数据发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据发送增、删、改的sql语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发送了变化)。这个整数就是行数
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的Result对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = conn.createStatement();
String sql = "insert into user(….) values(…..) ";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:
Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println(“删除成功!!!");
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:
Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println(“修改成功!!!");
}
CRUD操作-read
使用executeUpdate(String sql)方法完成数据查询操作,示例操作
Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}
代码实现
在src下建立资源文件db.properties
driver=com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456
1、提取工具类
package com.star.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty(JdbcUtils.driver);
url = properties.getProperty(JdbcUtils.url);
username = properties.getProperty(JdbcUtils.username);
password = properties.getProperty(JdbcUtils.password);
//驱动只需要加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getconnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接
public void release(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、编写增删改的方法,executeUpdate
package com.star.lesson02;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(4,'kuangshen','123456','24736743@qq.com','202001-01')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
package com.star.lesson02;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "DELETE FROM users WHERE id = 4";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
package com.star.lesson02;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "UPDATE users SET `NAME`='lenstar',`email`='1412265423@qq.com' WHERE id=1";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("修改成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询excuteQuery
package com.star.lesson02;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "SELECT `NAME` FROM USER WHERE id = 1";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql存在漏洞,会被攻击导致数据泄露,SQL会被拼接or
package com.star.lesson02;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
login("'or'1=1","123456");
}
//登录业务
public static void login(String username, String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "SELECT * FROM users WHERE `NAME` = '"+username+"' AND `PASSWORD`= '"+password+"'";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.print(rs.getString("NAME")+" ");
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5、PreparedeStatement对象
PreparedStatement可以防止SQL注入,效率更好
1、新增
package com.star.lesson03;
import com.star.lesson02.utils.JdbcUtils;
import java.util.Date;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();
//区别
//使用? 站位符代替参数
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4);
st.setString(2,"lenstar");
st.setString(3,"123456");
st.setString(4,"1412265423@qq.com");
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
2、删除
package com.star.lesson03;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();
//区别
//使用? 站位符代替参数
String sql = "DELETE FROM users WHERE id = ?";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、修改
package com.star.lesson03;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();
//区别
//使用? 站位符代替参数
String sql = "UPDATE users SET `NAME`='lenstar',`email`='1412265423@qq.com' WHERE id=?";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,1);
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("修改成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
4、查询
package com.star.lesson03;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();
String sql = "SELECT `NAME` FROM users WHERE id = ?";
st = conn.prepareStatement(sql);
st.setInt(1,2);
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5、防止SQL注入
package com.star.lesson03;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.*;
public class 防止SQL注入 {
public static void main(String[] args) {
login("''or '1=1'","123456");
}
//登录业务
public static void login(String username, String password) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getconnection();//获取数据库连接
String sql = "SELECT * FROM users WHERE `NAME` = ? AND `PASSWORD`= ?";
st = conn.prepareStatement(sql);//获得SQL的执行对象
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();
while(rs.next()){
System.out.print(rs.getString("NAME")+" ");
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
7、使用IDEA连接数据库
连接成功后,可以选择数据库
8、事务
ACID原则之前已经说过
代码实现
1、开启事务
2、一组业务执行完毕,提交事务
3、可以在catch语句中显示的定义回滚语句,但默认:失败就会回滚
/*创建账户表*/
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40),
money FLOAT
);
/*插入测试数据*/
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);
package com.star.lesson04;
import com.star.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getconnection();
//关闭数据库的自动提交,自动提交会开启事务
conn.setAutoCommit(false);
String sql1 = "update account set money = money-100 where name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update account set money = money+100 where name = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.rollback();//如果失败则回滚事务
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
9、数据库连接池
数据库连接--执行完毕--释放
连接--释放 这个过程是十分浪费资源的
池化技术:准备一些预先的资源,过来就连接预先准备好的
比如:
常用连接数为: 10
最小连接数就为: 10
最大连接数就为: 100 即业务最高承载上限
设置等带超时:100ms
编写连接池,实现一个接口 DateSourece
开源数据源实现(拿来即用)
DBCP
C3P0
Druid
使用了这些数据连接池之后,我们在项目开发中就不需要编写连接数据库的代码了!
DBCP
需要用到的jar包:
commons-dbcp-1.4 、 commons-pool-1.6
package com.star.lessson05.utils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源,工厂模式-->创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getconnection() throws SQLException {
return dataSource.getConnection();
}
//释放连接
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.star.lessson05;
import com.star.lessson05.utils.JdbcUtils_DBCP;
import java.util.Date;
import java.sql.*;
public class TestDBCP {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils_DBCP.getconnection();
//区别
//使用? 站位符代替参数
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4);
st.setString(2,"lenstar");
st.setString(3,"123456");
st.setString(4,"1412265423@qq.com");
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils_DBCP.release(conn,st,rs);
}
}
}
C3P0
需要用到的jar包:
c3p0-0.9.5.5、mchange-commons-java-0.2.19
package com.star.lessson05.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils_C3P0 {
private static DataSource dataSource = null;
static {
try {
// dataSource = new ComboPooledDataSource();
// ((ComboPooledDataSource) dataSource).setDriverClass();
// ((ComboPooledDataSource) dataSource).setUser();
// ((ComboPooledDataSource) dataSource).setPassword();
// ((ComboPooledDataSource) dataSource).setJdbcUrl();
// ((ComboPooledDataSource) dataSource).setMaxPoolSize();
// ((ComboPooledDataSource) dataSource).setMinPoolSize();
//创建数据源,工厂模式-->创建
dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getconnection() throws SQLException {
return dataSource.getConnection();
}
//释放连接
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.star.lessson05;
import com.star.lessson05.utils.JdbcUtils_C3P0;
import java.util.Date;
import java.sql.*;
public class TsetC3P0 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils_C3P0.getconnection();
//区别
//使用? 站位符代替参数
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4);
st.setString(2,"lenstar");
st.setString(3,"123456");
st.setString(4,"1412265423@qq.com");
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils_C3P0.release(conn,st,rs);
}
}
}
无论使用什么数据源,本质还是一样的,DateSource接口不会变,方法也不会变!