连接数据的步骤
模版
工具类:JdbcUtils.java(静态)
测试类:Base.java
使用:CRUD.java
CRUD
防止SQL注入PreparedStatement和Statement
数据库 jdbc
CREATE DATABASE jdbc;
USE jdbc;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`money` float unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;
insert into user(name,birthday,money)values('zhangsan','1987-10-01',400);
insert into user(name,birthday,money)values('lisi','1988-09-14',35);
insert into user(name,birthday,money)values('wangwu','1947-07-09',670);
连接数据的步骤------------------------------------------------------------------------------------------------
推荐这种方式,不会对具体的驱动类产生依赖。
会造成DriverManager中产生两个一样的驱动,并会对具体的驱动类产生依赖。
虽然不会对具体的驱动类产生依赖;但注册不太方便,所以很少使用。
Statement st = conn.createStatement();
st.executeQuery(sql);
String sql = “select * from table_name where col_name=?”;
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, “col_value”);
ps.executeQuery();ResultSet rs = statement.executeQuery(sql);
While(rs.next()){
rs.getString(“col_name”);
rs.getInt(“col_name”);
//…
}package com.dwt1220;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Base {
public static void main(String[] args) throws Exception{
test();
}
static void test() throws SQLException, ClassNotFoundException {
/***************/
/** 连接数据的步骤 *************/
/** 1.注册驱动 (只做一次) *************/
/** 2.建立连接(Connection) *************/
/** 3.创建执行SQL的语句(Statement) *************/
/** 4.执行语句 *************/
/** 5.处理执行结果(ResultSet) *************/
/** 6.释放资源 *************/
/** **************************/
/********** 1.注册驱动 (可加载多个驱动。建立连接时会依次搜索)*************************/
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
System.setProperty("jdbc.drivers", "driver1:driver2");
Class.forName("com.mysql.jdbc.Driver");// 推荐方式
/********** 2.建立连接 *************************/
/**
* url格式: JDBC:子协议:子名称//主机名:端口/数据库名?属性名=属性值&…
****/
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
/********** 3.创建执行SQL的语句(Statement) *************************/
/****1.创建声明Statement***/
String sql="select * from user";
Statement st=conn.createStatement();
// /****2.创建声明PreparedStatement***/
// String sql1 = "select * from user where id=?";
// PreparedStatement ps = conn.prepareStatement(sql1);
// ps.setString(1, "1");
/********** 4.执行语句 **********************************************/
ResultSet rs=st.executeQuery(sql);
// ps.executeQuery();//PreparedStatement方式
/********** 5.处理执行结果(ResultSet) **********************************************/
while(rs.next()){
System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"
+rs.getObject(3)+"\t"+rs.getObject(4));
}
/********** 6.释放资源 **********************************************/
rs.close();
st.close();
conn.close();
}
}
模版 --------------------------------------------------------------------------------
工具类:JdbcUtils.java(静态)
测试类:Base.java
使用:CRUD.java
JdbcUtils.java(静态)
package com.dwt1220;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
public final class JdbcUtils {
private static String url = "jdbc:mysql://localhost:3306/jdbc";
private static String user = "root";
private static String password = "123456";
private JdbcUtils() {
}
static {// 静态代码快,只执行一次。
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Base.java
package com.dwt1220;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Base {
public static void main(String[] args) throws Exception {
template();
}
static void template() throws Exception {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//2.建立连接
conn=JdbcUtils.getConnection();
//3.创建语句
st=conn.createStatement();
//4.执行语句
rs=st.executeQuery("select * from user");
//5.处理结果
while (rs.next()) {
System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t"
+ rs.getObject(3) + "\t" + rs.getObject(4));
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void test() throws SQLException, ClassNotFoundException {
/***************/
/** 连接数据的步骤 *************/
/** 1.注册驱动 (只做一次) *************/
/** 2.建立连接(Connection) *************/
/** 3.创建执行SQL的语句(Statement) *************/
/** 4.执行语句 *************/
/** 5.处理执行结果(ResultSet) *************/
/** 6.释放资源 *************/
/** **************************/
/********** 1.注册驱动 (可加载多个驱动。建立连接时会依次搜索) *************************/
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
System.setProperty("jdbc.drivers", "driver1:driver2");
Class.forName("com.mysql.jdbc.Driver");// 推荐方式
/********** 2.建立连接 *************************/
/**
* url格式: JDBC:子协议:子名称//主机名:端口/数据库名?属性名=属性值&…
****/
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
/********** 3.创建执行SQL的语句(Statement) *************************/
/**** 1.创建声明Statement ***/
String sql = "select * from user";
Statement st = conn.createStatement();
// /****2.创建声明PreparedStatement***/
// String sql1 = "select * from user where id=?";
// PreparedStatement ps = conn.prepareStatement(sql1);
// ps.setString(1, "1");
/********** 4.执行语句 **********************************************/
ResultSet rs = st.executeQuery(sql);
// ps.executeQuery();//PreparedStatement方式
/********** 5.处理执行结果(ResultSet) **********************************************/
while (rs.next()) {
System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t"
+ rs.getObject(3) + "\t" + rs.getObject(4));
}
/********** 6.释放资源 **********************************************/
rs.close();
st.close();
conn.close();
}
}
JdbcUtilsSing.java(单例)
package com.dwt1220;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
public final class JdbcUtilsSing {
private static String url = "jdbc:mysql://localhost:3306/jdbc";
private static String user = "root";
private static String password = "123456";
private static JdbcUtilsSing instance = null;
private JdbcUtilsSing() {
}
public static JdbcUtilsSing getJdbcUtilsSting() {
if (instance == null) {
synchronized (JdbcUtilsSing.class) {
if (instance == null) {
instance = new JdbcUtilsSing();
}
}
}
return instance;
}
static {// 静态代码快,只执行一次。
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
CRUD.java(最好使用PreparedStatement的CRUD)
package com.dwt1220;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CRUD {
public static void main(String[] args) throws Exception {
create();
update();
delete();
read();
}
static void read() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
st = conn.createStatement();
// 4.执行语句
rs = st.executeQuery("select id,name,birthday,money from user");
// 5.处理结果
while (rs.next()) {
System.out.println(rs.getObject("id") + "\t"
+ rs.getObject("name") + "\t"
+ rs.getObject("birthday") + "\t"
+ rs.getObject("money"));
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void create() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
st = conn.createStatement();
String sql="insert into user(name,birthday,money)values('name1','1987-10-01',400)";
// 4.执行语句
int i=st.executeUpdate(sql);
System.out.println("i="+i);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void update() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
st = conn.createStatement();
String sql="update user set money=money+10";
// 4.执行语句
int i=st.executeUpdate(sql);
System.out.println("i="+i);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void delete() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
st = conn.createStatement();
String sql="delete from user where id>4";
// 4.执行语句
int i=st.executeUpdate(sql);
System.out.println("i="+i);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
}
防止SQL注入PreparedStatement和Statement --------------------------------------------------------------------
1.没有SQL注入的问题。
2.Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出。
3.数据库和驱动可以对PreperedStatement进行优化(只有在相关联的数据库连接没有关闭的情况下有效)。
CRUD.java(使用PreparedStatement)package com.dwt1220;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CRUD {
public static void main(String[] args) throws Exception {
read("zhangsan");
}
static void read(String name) throws SQLException {
Connection conn = null;
PreparedStatement ps = null;//使用PreparedStatement防止SQL注入
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
String sql="select id,name,birthday,money from user where name=?";
ps = conn.prepareStatement(sql);
ps.setString(1, name);
// 4.执行语句
rs = ps.executeQuery();
// 5.处理结果
while (rs.next()) {
System.out.println(rs.getObject("id") + "\t"
+ rs.getObject("name") + "\t"
+ rs.getObject("birthday") + "\t"
+ rs.getObject("money"));
}
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
static void create() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
st = conn.createStatement();
String sql="insert into user(name,birthday,money)values('name1','1987-10-01',400)";
// 4.执行语句
int i=st.executeUpdate(sql);
System.out.println("i="+i);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void update() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
st = conn.createStatement();
String sql="update user set money=money+10";
// 4.执行语句
int i=st.executeUpdate(sql);
System.out.println("i="+i);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void delete() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
// 3.创建语句
st = conn.createStatement();
String sql="delete from user where id>4";
// 4.执行语句
int i=st.executeUpdate(sql);
System.out.println("i="+i);
} finally {
JdbcUtils.free(rs, st, conn);
}
}
}