JDBC操控MySQL
1，JDBC的作用
作用：Java 程序员通过JDBC操控数据库，而不用设计具体的数据库驱动，使用时只需导入一个数据库的驱动包，调用特定的接口即可
2，JDBC操控数据基本流程
package com.nextdoor.mysql.h;
import java.sql.*;
//本质和用SQL yog的逻辑是一样的
public class MyFirstJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和URL
String url = "jdbc:mysql://localhost:3306/oooo?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "password";
//3.连接成功，获取数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.创建执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象，去执行SQL,同时返回结果集
String sql ="SELECT * FROM result";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("score="+resultSet.getObject("score"));
System.out.println("=================================================");
}
//6.断开连接
resultSet.close();
statement.close();
connection.close();
}
}
//返回的结果
id=1
score=97
=================================================
id=2
score=98
=================================================
id=3
score=65
=================================================
id=4
score=75
=================================================
id=5
score=76
================================================
3，JDBC各类详解
Class.forName("com.mysql.jdbc.Driver"); // 加载MySQL驱动固定语句
String url = "jdbc:mysql://localhost:3306/oooo?useUnicode=true&characterEncoding=utf8&useSSL=true";
/* jdbc:mysql:// //类似于域名
localhost:3306/oooo //主机地址
useUnicode=true //使用中文
characterEncoding=utf8 //字符集为utf8
useSSL=true //使用安全的连接，减少错误
*/
Connection 类
// 数据库类
Connection connection = DriverManager.getConnection(url, username, password);
connection.commit(); //事务提交
connection.rollback(); //回滚
Statement 类
// SQL执行对象
Statement statement = connection.createStatement(); // 通过connection 对象创建
statement.executeQuery(); //执行查询操作
statement.executeUpdate(); //执行更新，插入，删除操作
statement.execute(); //执行所有操作，效率相对较低
// 不安全的一个类，不推荐使用
ResultSet 类
// 返回的结果集
// 本质是一个链表
resultSet.next(); //往后一个
resultSet.previous(); //往前一个
resultSet.afterLast(); // 最后一个
resultSet.beforeFirst(); //第一个
resultSet.absolute(index);//指定某一个
//获取指定的结果，Object 为 未知
resultSet.getObject("");
resultSet.getInt("");
resultSet.getString("");
resultSet.getDouble("");
resultSet.getDate("");
4，工具类的实现
package com.nextdoor.mysql.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 usename =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("driver");
url = properties.getProperty("url");
usename = properties.getProperty("usename");
password = properties.getProperty("password");
//加载驱动，放在static代码块中，表示只需要加载一次即可
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
// 连接数据库
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,usename,password);
}
// 释放资源
public static void release(Connection conn, ResultSet rs, Statement st){
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.nextdoor.mysql.h;
import com.nextdoor.mysql.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();
//获得SQl的执行对象
st =conn.createStatement();
String sql = "INSERT INTO `result`(`id`,`score`) VALUES ('6','120')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,rs,st);
}
}
}
5，Statement安全隐患 MySQL注入
含义：web数据库对用户输入的合法性判断有误，或过滤不严，从而导致数据库数据泄露
package com.nextdoor.mysql.h;
import com.nextdoor.mysql.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSQLzhuru {
//字符串拼接导致的SQL注入
public static void main(String[] args) {
Connection conn =null;
ResultSet rs =null;
Statement st = null;
try {
conn = JdbcUtils.getConnection();
st= conn.createStatement();
String sql = "select * from result where `id`='' or '1=1'";
ResultSet resultSet = st.executeQuery(sql);
while(resultSet.next()){
System.out.println(resultSet.getObject("id"));
}
// ' or '1=1 注入手段
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,rs,st);
}
}
}
//output
1
2
3
4
5
6
6，PrepareStatement 类详解
使用方法：
package com.nextdoor.mysql.h;
import com.nextdoor.mysql.utils.JdbcUtils;
import com.sun.javaws.IconUtil;
import java.sql.*;
public class TestSQLzhuru {
//字符串拼接导致的SQL注入
public static void main(String[] args) {
Connection conn =null;
ResultSet rs =null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//编写SQL
String sql ="INSERT INTO `result`(`id`,`score`) VALUES (?,?)";
//SQL预编译
st = conn.prepareStatement(sql);
//手动设置SQL参数
st.setInt(1,7);
st.setInt(2,120);
//执行返回结果
int resultSet = st.executeUpdate();
System.out.println(resultSet);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,rs,st);
}
}
}
- PrepareStatement类如何防止SQL注入：
把传递进来的参数当作字符，假如其中存在转义字符，会被直接转义
7，JDBC实现事务
package com.nextdoor.mysql.h;
import com.nextdoor.mysql.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();
//关闭自动提交，自动开始事务
conn.setAutoCommit(false);
//获得SQl的执行对
st =conn.createStatement();
String sql = "INSERT INTO `result`(`id`,`score`) VALUES ('6','120')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
//事务提交
conn.commit();
} catch (SQLException e) {
//显示化事务回滚，可以不写，会默认使用
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
//释放资源
JdbcUtils.release(conn,rs,null);
}
}
}
8，DBCP C3PO 数据库连接池
数据库连接池的作用：利用池化技术，节省数据库连接和释放时带来的资源损耗，节省系统开销
连接池的实现类：本质是实现了DataSource这个接口
常用的实现类：DBCP，C3PO Druid
DBCP：
第三方Jar包：commons-dbcp-1.4
commons-pool-1.6
#连接设置
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/oooo?useUnicode=true&characterEncoding=utf8&useSSL=true
jdbc.username=root
jdbc.password=password
#<!-- 初始化连接 -->
dataSource.initialSize=10
#<!-- 最大空闲连接 -->
dataSource.maxIdle=20
#<!-- 最小空闲连接 -->
dataSource.minIdle=5
#最大连接数量
dataSource.maxActive=50
package com.nextdoor.mysql.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 String driver =null;
private static String url =null;
private static String usename =null;
private static String password =null;
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, ResultSet rs, PreparedStatement st){
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.nextdoor.mysql.h;
import com.nextdoor.mysql.utils.JdbcUtils;
import com.nextdoor.mysql.utils.JdbcUtils_Dbcp;
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_Dbcp.getConnection();
//关闭自动提交，自动开始事务
conn.setAutoCommit(false);
//获得SQl的执行对象
st =conn.createStatement();
String sql = "INSERT INTO `result`(`id`,`score`) VALUES ('6','120')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
//事务提交
conn.commit();
} catch (SQLException e) {
//显示化事务回滚，可以不写，会默认使用
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
//释放资源
JdbcUtils_dbcp.release(conn,rs,null);
}
}
}
C3PO:
第三方Jar包：c3p0-0.9.5.5
mchange-commons-java-0.2.19
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
<property name="user">root</property>
<property name="password">java</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
</default-config>
<named-config name="mySource">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/bookstore</property>
<property name="user">root</property>
<property name="password">xxxx</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
</named-config>
</c3p0-config>
package com.nextdoor.mysql.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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JdbcUtils_c3P0 {
private static String driver =null;
private static String url =null;
private static String usename =null;
private static String password =null;
private static DataSource dataSource=null;
static{
try {
dataSource = new ComboPooledDataSource("mySource");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
public static void release(Connection conn, ResultSet rs, PreparedStatement st){
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.nextdoor.mysql.h;
import com.nextdoor.mysql.utils.JdbcUtils;
import com.nextdoor.mysql.utils.JdbcUtils_Dbcp;
import com.nextdoor.mysql.utils.JdbcUtils_c3P0;
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_c3P0.getConnection();
//关闭自动提交，自动开始事务
conn.setAutoCommit(false);
//获得SQl的执行对象
st =conn.createStatement();
String sql = "INSERT INTO `result`(`id`,`score`) VALUES ('6','120')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
//事务提交
conn.commit();
} catch (SQLException e) {
//显示化事务回滚，可以不写，会默认使用
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
//释放资源
JdbcUtils_c3P0.release(conn,rs,null);
}
}
}
203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
