1. 数据准备
JDBC API
- DriverManager:驱动程序管理类,用来装载驱动程序,为创建数据库连接提供支持
- Connection:接口。连接某一个数据库
- Statement:接口。提供了执行SQL语句获取查询结果的方法。
- PreparedStatement:用于执行预编译的SQL语句
- ResultSet:接口。对结果集进行处理的方法
# jsp_db
# auto_increment:自增长
# primary key:主键
# engine:存储引擎
create table tbl_user(
id int(11) unsigned not null auto_increment,
name varchar(50) not null default ' ',
password varchar(50) not null default ' ',
email varchar(50) default ' ',
primary key (id))
engine = InnoDB
default charset = utf8;
create table tbl_address (
id int(11) unsigned not null auto_increment,
city varchar(20) default null,
country varchar(20) default null,
user_id int(11) unsigned not null,
primary key (id)
)
engine=innodb
default charset = utf8;
insert into tbl_user(id, name, password, email)
values
(1, 'xiaoming', '123456', 'xiaoming@email.com'),
(2, 'xiaozhang', '12345', 'xiaozhang@email.com');
select * from tbl_user;
insert into tbl_address(city, country, user_id)
values ('beijing', 'china', '1');
insert into tbl_address(city, country, user_id)
values ('tianjing', 'china', '2');
select * from tbl_address;
2. 数据查询
下载JDBC驱动
https://dev.mysql.com/downloads/connector/j/
JDBC编程的5个步骤
- 加载驱动
- 打开连接
- 执行查询
- 处理结果
- 清理环境
eclipse代码提示
alt+/
自动引入包
代码区右键 -> Source -> Organize Imports
在项目中使用JDBC
- 赋值JDBC到项目中的 WebContent->WEB-INF->lib
- 项目右键 Propertis -> Java Build Path -> Libraries -> Add JARs -> 选择刚刚复制到项目的JDBC
package com.jikexueyuan.jdbc;
import java.sql.DriverManager;
import java.sql.ResultSet;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Driver;
import com.mysql.jdbc.Statement;
public class JDBCTest {
public static void main(String[] args) {
String sql = "SELECT * FROM tbl_user";
Connection connection = null; // 当前的数据库连接
Statement statement = null; // 向数据库发送sql语句
ResultSet rSet = null; // 结果集
try {
// 注册mysql的驱动程序
Class.forName("com.mysql.jdbc.Driver");
connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "100300");
statement = (Statement) connection.createStatement();
rSet = statement.executeQuery(sql);
while (rSet.next()) {
System.out.print(rSet.getInt("id") + " ");
System.out.print(rSet.getString("name") + " ");
System.out.print(rSet.getString("password") + " ");
System.out.print(rSet.getString("email") + " ");
System.out.println();
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
try {
rSet.close();
} catch ( Exception e2) {
}
try {
statement.close();
} catch (Exception e3) {
}
try {
connection.close();
} catch (Exception e4) {
}
}
}
}
3. 数据更新
package com.jikexueyuan.jdbc;
import java.sql.DriverManager;
import java.sql.ResultSet;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Driver;
import com.mysql.jdbc.Statement;
public class JDBCTest {
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "100300");
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
// 插入数据
public static void insert() {
Connection connection = getConnection();
try {
String sql = "INSERT INTO tbl_user(name, password, email)" + "VALUES('Tom', '123456', 'tom@gmail.com')";
Statement st = (Statement) connection.createStatement();
int count = st.executeUpdate(sql);
System.out.println("向用户表中插入了 " + count + " 条记录");
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 更新数据
public static void update() {
Connection connection = getConnection();
try {
String sql = "UPDATE tbl_user SET email='tom@126.com' WHERE name = 'Tom'";
Statement st = (Statement) connection.createStatement();
int count = st.executeUpdate(sql);
System.out.println("向用户表中更新了 " + count + " 条记录");
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 删除
public static void delete() {
Connection connection = getConnection();
try {
String sql = "DELETE FROM tbl_user WHERE name = 'Tom'";
Statement st = (Statement) connection.createStatement();
int count = st.executeUpdate(sql);
System.out.println("向用户表中删除了 " + count + " 条记录");
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
// insert();
// update();
delete();
}
}
4. 事务处理
事务的四个特征
- 原子性
- 一致性
- 隔离性
- 持久性
事务的语句
- 开始事务:BEGIN TRANSACTION
- 提交事务:COMMIT TRANSACTION
- 回滚事务:ROLLBACK TRANSACTION
Eclipse格式化代码
Comm+Shift+F
package com.jikexueyuan.jdbc;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
public class TransactionTest {
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "100300");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void insertUserData(Connection conn) throws SQLException {
String sql = "INSERT INTO tbl_user(id, name, password, email)" + "VALUES(10, 'TOM', '123456', 'tom@126.com')";
Statement st = (Statement) conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println("向用户表中插入了 " + count + " 条数据");
}
public static void insertAddressData(Connection conn) throws SQLException {
String sql = "INSERT INTO tbl_address(id, city, country, user_id)" + "VALUES(1, 'shanghai', 'china', '10')";
Statement st = (Statement) conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println("向地址表中插入了 " + count + " 条数据");
}
public static void main(String[] args) {
Connection conn = null;
try {
conn = getConnection();
conn.setAutoCommit(false); // 禁止事务的自动提交
insertUserData(conn);
insertAddressData(conn);
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("=========捕获到SQL异常===========");
e.printStackTrace();
try {
conn.rollback(); // 回滚事务
System.out.println("=========回滚事务成功===========");
} catch (Exception e2) {
e2.printStackTrace();
}
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (Exception e3) {
e3.printStackTrace();
}
}
}
}
5. 程序优化(上)
// dbconfig.properties
driver=com.mysql.jdbc.Driver
dburl=jdbc\:mysql\://localhost\:3306/jsp_db
user=root
password=100300
// ConnectionFactory.class
package com.jikexueyuan.util;
import java.io.InputStream;
import java.sql.DriverManager;
import java.util.Properties;
import com.mysql.jdbc.Connection;
public class ConnectionFactory {
private static String driver;
private static String dburl;
private static String user;
private static String password;
private static final ConnectionFactory factory = new ConnectionFactory();
private Connection conn;
// 静态代码块,只会执行一次
static {
Properties prop = new Properties();
try {
InputStream in = ConnectionFactory.class.getClassLoader().getResourceAsStream("dbconfig.properties");
prop.load(in);
} catch (Exception e) {
System.out.println("=======配置文件读取错误========");
e.printStackTrace();
}
driver = prop.getProperty("driver");
dburl = prop.getProperty("dburl");
user = prop.getProperty("user");
password = prop.getProperty("password");
}
private ConnectionFactory() {
}
public static ConnectionFactory getInstance() {
return factory;
}
public Connection makeConnection() {
try {
Class.forName(driver);
conn = (Connection) DriverManager.getConnection(dburl, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
// ConnectionFactoryTest.class
package com.jikexueyuan.util;
import java.io.InputStream;
import java.sql.DriverManager;
import java.util.Properties;
import com.mysql.jdbc.Connection;
public class ConnectionFactory {
private static String driver;
private static String dburl;
private static String user;
private static String password;
private static final ConnectionFactory factory = new ConnectionFactory();
private Connection conn;
// 静态代码块,只会执行一次
static {
Properties prop = new Properties();
try {
InputStream in = ConnectionFactory.class.getClassLoader().getResourceAsStream("dbconfig.properties");
prop.load(in);
} catch (Exception e) {
System.out.println("=======配置文件读取错误========");
e.printStackTrace();
}
driver = prop.getProperty("driver");
dburl = prop.getProperty("dburl");
user = prop.getProperty("user");
password = prop.getProperty("password");
}
private ConnectionFactory() {
}
public static ConnectionFactory getInstance() {
return factory;
}
public Connection makeConnection() {
try {
Class.forName(driver);
conn = (Connection) DriverManager.getConnection(dburl, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
6. 优化(下)
// UserDao.java
package com.jikexueyuan.dao;
import java.sql.SQLException;
import com.jikexueyuan.entity.User;
import com.mysql.jdbc.Connection;
public interface UserDao {
public void save(Connection conn, User user) throws SQLException;
public void update(Connection conn, Long id, User user) throws SQLException;
public void delete(Connection conn, User user) throws SQLException;
}
// UserDaoImpl.java
package com.jikexueyuan.dao.impl;
import java.sql.SQLException;
import com.jikexueyuan.dao.UserDao;
import com.jikexueyuan.entity.User;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class UserDaoImpl implements UserDao {
// 保存用户信息
@Override
public void save(Connection conn, User user) throws SQLException {
PreparedStatement ps = (PreparedStatement) conn.prepareCall("INSERT INTO tbl_user(name, password, email) VALUES (?,?,?)");
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
ps.execute();
}
// 根据用户ID更新用户信息
@Override
public void update(Connection conn, Long id, User user) throws SQLException {
String updateSql = "UPDATE tbl_user SET name = ?, password = ?, email = ? WHERE id = ?";
PreparedStatement ps = (PreparedStatement) conn.prepareStatement(updateSql);
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
ps.setLong(4, id);
}
// 删除用户
@Override
public void delete(Connection conn, User user) throws SQLException {
PreparedStatement ps = (PreparedStatement) conn.prepareStatement("DELETE FROM tbl_user WHERE id = ?");
ps.setLong(1, user.getId());
ps.execute();
}
}
// UserDaoTest.java
package com.jikexueyuan.test;
import com.jikexueyuan.dao.UserDao;
import com.jikexueyuan.dao.impl.UserDaoImpl;
import com.jikexueyuan.entity.User;
import com.jikexueyuan.util.ConnectionFactory;
import com.mysql.jdbc.Connection;
public class UserDaoTest {
public static void main(String[] args) {
Connection conn = null;
try {
conn = ConnectionFactory.getInstance().makeConnection();
conn.setAutoCommit(false);
UserDao userDao = new UserDaoImpl();
User tom = new User();
tom.setName("Tom");
tom.setPassword("123456");
tom.setEmail("tom.gmail.com");
userDao.save(conn, tom);
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}