文章目录提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
- JDBC
- SQL 注入问题
- Java实现事物
- 数据库连接池
- DBCP连接
- C3P0连接
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')
测试
package com.yyy.jdbc01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo01 {
public static void main(String[] args) throws Exception {
//1. 加载驱动
Class.forName("com.mysql.jdbc.NonRegisteringDriver"); // 固定写法，加载驱动
//2.用户信息和url
// ?useUnicode=true
// &characterEncoding=utf8
// &useSSl=true
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";
String username="root";
String password="123456";
//3. 连接成功，数据库对象 connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4. 执行SQL的对象 statement
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")); // 在不知道列类型的情况下使用getObject
System.out.println("name= "+resultSet.getObject("name"));
System.out.println("pwd= "+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();
}
}
Statement 和 PreparedStatement 执行SQL的区别
preparedStatement.executeQuery(); //查询操作，返回resultSet preparedStatement.execute(); //执行任何操作 preparedStatement.executeUp(); // 更新，插入，删除都用这个，返回受影响的行数
创建工具类实现SQL的增，删，改，查
配置文件按 db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true username=root password=123456
工具类 JdbcUtils
package com.yyy.jdbc02.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);
in.close();
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//驱动只用加载一次
Class.forName(driver);
System.out.println("static执行~");
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
// 释放资源
public static void relaase(Connection conn, Statement st, ResultSet rs) {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st!=null) {
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
增，删，改，查
package com.yyy.jdbc02;
import com.yyy.jdbc02.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的执行对象
// 增
long l = st.executeLargeUpdate("INSERT INTO users(id,NAME,PASSWORD,email,birthday)" +
"VALUES(4,'kuangshen','123456','zs@sina.com','1980-12-04')");
if (l>0) {
System.out.println("插入信息成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.relaase(conn,st,rs);
}
}
}
SQL 注入问题
package com.yyy.jdbc02;
import com.yyy.jdbc02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSQLZhuRu {
public static void main(String[] args) {
login(" 'or' 1=1"," 'or' 1=1");
// login("kuangshen","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的执行对象
rs = st.executeQuery("select * from users where name='"+username+" ' and password= '"+password+"'");
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("-------------------------");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.relaase(conn,st,rs);
}
}
}
// 执行结果 打印了所有的信息
kuangshen
123456
-------------------------
lisi
123456
-------------------------
wangwu
123456
-------------------------
PreparedStatement 解决SQL注入问题
package com.yyy.jdbc03;
import com.mysql.jdbc.PreparedStatement;
import com.yyy.jdbc02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSQLZhuRuPrepare {
public static void main(String[] args) {
login(" 'or 1=1"," ''or 1=1");
// login("lisi","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 = (PreparedStatement) conn.prepareStatement(sql); // 获得SQl的执行对象
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
System.out.println("-------------------------");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.relaase(conn,st,rs);
}
}
}
PreparedStatement 防止SQL注入的本质：把传递进来的参数当作字符。假设存在转义字符就忽略了
Java实现事物成功
package com.yyy.jdbc04;
import com.mysql.jdbc.PreparedStatement;
import com.yyy.jdbc02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
public class TestTransaction01 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
// 关闭数据库的自动提交，自动会开启事物
connection.setAutoCommit(false);
String sql ="update account set money=money-100 where name='A'";
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
int i = preparedStatement.executeUpdate();
String sql1 ="update account set money=money+100 where name='B'";
preparedStatement = (PreparedStatement) connection.prepareStatement(sql1);
int i1 = preparedStatement.executeUpdate();
connection.commit(); //业务完毕，提交事物
if (i>0 && i1>0) {
System.out.println("成功！");
}
} catch (SQLException e) {
try {
connection.rollback(); // 如果事物提交失败，就回滚
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
} finally {
JdbcUtils.relaase(connection,preparedStatement,null);
}
}
}
失败
package com.yyy.jdbc04;
import com.mysql.jdbc.PreparedStatement;
import com.yyy.jdbc02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
public class TestTransaction02 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
// 关闭数据库的自动提交，自动会开启事物
connection.setAutoCommit(false);
String sql ="update account set money=money-100 where name='A'";
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
int i = preparedStatement.executeUpdate();
int x=1/0; // 报错，失败回滚
String sql1 ="update account set money=money+100 where name='B'";
preparedStatement = (PreparedStatement) connection.prepareStatement(sql1);
int i1 = preparedStatement.executeUpdate();
connection.commit(); //业务完毕，提交事物
if (i>0 && i1>0) {
System.out.println("成功！");
}
} catch (SQLException e) {
// 默认失败，回滚,可以不用手动写
// try {
// connection.rollback(); // 如果事物提交失败，就回滚
// } catch (SQLException ex) {
// throw new RuntimeException(ex);
// }
throw new RuntimeException(e);
} finally {
JdbcUtils.relaase(connection,preparedStatement,null);
}
}
}
数据库连接池
数据库连接—执行完毕—释放
连接–>释放 这个过程十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
最大连接数：
最小连接数：
等待超时：
编写连接池，实现一个接口 DataSource
DBCP连接dbcp-config.properties 配置文件
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true username=root password=acer initialSize=10 maxActive=50 maxIdle=20 minIdle=5 # 等待连接超时 60000ms/1000 = 60s maxWait=60000 connectionProperties=useUnicode=true;characterEncoding=UTF8 defaultAutoCommit=true defaultTransactionIslation=READ_UNCOMMITTED
工具类
package com.yyy.dbcp01.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 relaase(Connection conn, Statement st, ResultSet rs) {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st!=null) {
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
测试类
package com.yyy.dbcp01;
import com.yyy.dbcp01.utils.JdbcUtils_DBCP;
import com.yyy.jdbc02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDBCP {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils_DBCP.getConnection(); // 连接数据库
st = conn.createStatement(); // 获得SQl的执行对象
long l = st.executeUpdate("INSERT INTO users(id,NAME,PASSWORD,email,birthday)" +
"VALUES(4,'kuangshen','123456','zs@sina.com','1980-12-04')");
if (l>0) {
System.out.println("插入信息成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils_DBCP.relaase(conn,st,rs);
}
}
}
C3P0连接
c3p0-config.xml 配置文件
com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/jdbcstudy root acer 10 20
工具类
package com.yyy.c3p0.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
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_C3P0 {
private static DataSource dataSource = null;
static {
try {
dataSource = new ComboPooledDataSource();
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); // 从数据源中获取连接
}
// 释放资源
public static void relaase(Connection conn, Statement st, ResultSet rs) {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st!=null) {
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
测试类
package com.yyy.c3p0;
import com.yyy.c3p0.utils.JdbcUtils_C3P0;
import com.yyy.dbcp01.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestC3P0 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils_C3P0.getConnection(); // 连接数据库
st = conn.createStatement(); // 获得SQl的执行对象
long l = st.executeUpdate("INSERT INTO users(id,NAME,PASSWORD,email,birthday)" +
"VALUES(5,'kuangshen','123456','zs@sina.com','1980-12-04')");
if (l>0) {
System.out.println("插入信息成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils_C3P0.relaase(conn,st,rs);
}
}
}
