创建数据库和表
create table user_tbl(
id int(11) unsigned not null auto_increment primary key,
name varchar(50) not null default "",
password varchar(50) not null default "",
email varchar(50) default "")
engine = InnoDB
default charset = utf8;
insert into user_tbl(id,name,password,email) values
(1,"haha","haha",""),(2,"hehe","hehe","");
连接数据库步骤
加载JDBC驱动,创建数据库链接
private void DBConnection(){
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
}catch(Exception e){
e.printStackTrace();
}
}
创建Statement,执行sql语句,处理结果
//查询
public void select(){
sql = "select * from user_tbl";
try{
DBConnection();
st = conn.createStatement();
//执行查询
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt("id")+" "
+rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
close();
}
}
关闭JDBC对象
public void close(){
try{
if(rs!=null) rs.close();
if(st!=null) st.close();
if(conn!=null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
完整代码
package com.demo;
import java.sql.*;
import com.sun.org.apache.regexp.internal.recompile;
public class JDBCTest {
private Connection conn = null;
private String sql = "";
private Statement st = null;
private ResultSet rs = null;
//mysql驱动
private String driver = "com.mysql.jdbc.Driver";
//javaweb_db为数据库名
private String url = "jdbc:mysql://localhost:3306/javaweb_db";
private String user = "root";
private String password = "1234";
//获取数据库连接
private void DBConnection(){
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
}catch(Exception e){
e.printStackTrace();
}
}
//关闭JDBC对象
public void close(){
try{
if(rs!=null) rs.close();
if(st!=null) st.close();
if(conn!=null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
//查询
public void select(){
sql = "select * from user_tbl";
try{
DBConnection();
st = conn.createStatement();
//执行查询
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt("id")+" "
+rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
close();
}
}
//插入
public void add(){
sql = "insert into user_tbl(id,name,password,email) values (4,'rrr','rrr','')";
try{
DBConnection();
st = conn.createStatement();
//执行更新
st.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}finally{
close();
}
}
public void update(){}
public void delete(){}
public static void main(String[] args) {
JDBCTest jdbc= new JDBCTest();
// jdbc.select();
jdbc.add();
}
}
事务处理
事务是指由一条或多条对数据库更新的sql语句所组成的一个不可分割的工作单元。只有当事务中的所有操作都正常完成了,整个事务才能被提交到数据库,如果有一项操作没有完成,就必须撤消整个事务。在jdbc api中,默认的情况为自动提交事务,也就是说,每一条对数据库的更新的sql语句代表一项事务,操作成功后,系统自动调用commit()来提交,否则将调用rollback()来撤消事务。可以通过调用setAutoCommit(false)来禁止自动提交事务。然后就可以把多条更新数据库的sql语句做为一个事务,在所有操作完成之后,调用commit()来进行整体提交。倘若其中一项sql操作失败,就不会执行commit()方法,而是产生相应的sqlexception,此时就可以捕获异常代码块中调用rollback()方法撤消事务。
user_tbl表中的数据
修改
package com.demo;
import java.sql.*;
public class JDBCTest {
private String sql = "";
private Statement st = null;
private ResultSet rs = null;
//mysql驱动
private String driver = "com.mysql.jdbc.Driver";
//javaweb_db为数据库名
private String url = "jdbc:mysql://localhost:3306/javaweb_db";
private String user = "root";
private String password = "1234";
//获取数据库连接
private Connection DBConnection(){
Connection conn = null;
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
//关闭JDBC对象
public void close(){
try{
if(rs!=null) rs.close();
if(st!=null) st.close();
}catch(Exception e){
e.printStackTrace();
}
}
//查询
public void select(Connection conn){
sql = "select * from user_tbl";
try{
DBConnection();
st = conn.createStatement();
//执行查询
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt("id")+" "
+rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
close();
}
}
//插入
public void add(Connection conn) throws SQLException{
sql = "insert into user_tbl(id,name,password,email) values (4,'rrr','rrr','')";
st = conn.createStatement();
//执行更新
st.executeUpdate(sql);
close();
}
public void update(){
}
//刪除
public void delete(Connection conn) throws SQLException{
sql = "delete from user_tbl where id = '1'";
st = conn.createStatement();
//执行更新
st.executeUpdate(sql);
close();
}
public static void main(String[] args) {
JDBCTest jdbc= new JDBCTest();
Connection conn = null;
try{
conn = jdbc.DBConnection();
//设置事务自动提交为false
conn.setAutoCommit(false);
jdbc.add(conn);
jdbc.delete(conn);
//事务提交
conn.commit();
}catch(Exception e){
System.out.println("========捕获异常");
e.printStackTrace();
try {
conn.rollback();
System.out.println("========事务回滚成功");
} catch (Exception e2) {
e2.printStackTrace();
}
}finally{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
捕获到异常,id=4的记录已存在,无法插入
查看数据表,Delete被撤销
从属性文件中读取数据库配置
- 在src中新建properties,内容为:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/javaweb_db
user=root
password=1234
- 新建ConnectionFactory类
加载配置文件
Properties properties = new Properties();
try {
InputStream in = ConnectionFactory.class.getClassLoader()
.getResourceAsStream("dbconfig.properties");
properties.load(in);
} catch (IOException e) {
System.out.println("=====配置文件加载错误=====");
}
获取配置属性
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
实现工厂单例模式
private static final ConnectionFactory factory = new ConnectionFactory(); private ConnectionFactory(){} //获得连接工厂实例 public static ConnectionFactory getInstance(){ return factory; }
完整代码
package com.db.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionFactory {
private static final ConnectionFactory factory = new ConnectionFactory();
private Connection conn = null;
private static String driver = null;
private static String url = null;
private static String user = null;
private static String password = null;
static{
//加载配置文件
Properties properties = new Properties();
try {
InputStream in = ConnectionFactory.class.getClassLoader()
.getResourceAsStream("dbconfig.properties");
properties.load(in);
} catch (IOException e) {
System.out.println("=====配置文件加载错误=====");
}
//获取配置属性
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
}
private ConnectionFactory(){
}
//获得连接工厂实例
public static ConnectionFactory getInstance(){
return factory;
}
//获得数据库连接
public Connection getConnection(){
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return conn;
}
}
- 编写测试类
package com.db.test;
import java.sql.Connection;
import java.sql.SQLException;
import com.db.util.ConnectionFactory;
public class ConfigTest {
public static void main(String[] args) {
Connection conn = ConnectionFactory.getInstance().getConnection();
try {
System.out.println(conn.getAutoCommit());
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
返回结果为true
结构优化
创建DTO类,对应数据库中的表
package com.db.entity;
public abstract class IdEntity {
protected long id;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
}
package com.db.entity;
public class UserEntity extends IdEntity{
private String name;
private String password;
private String email;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "UserEntity [name=" + name + ", password=" + password
+ ", email=" + email + ", id=" + id + "]";
}
}
创建DAO接口和实现类,用于访问数据库及增删查改等操作
package com.db.dao;
import java.sql.Connection;
import java.sql.SQLException;
import com.db.entity.UserEntity;
public interface UserDao {
public void insert(Connection conn, UserEntity user) throws SQLException;
public void update(Connection conn, UserEntity user) throws SQLException;
public void delete(Connection conn, UserEntity user) throws SQLException;
}
package com.db.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.db.dao.UserDao;
import com.db.entity.UserEntity;
public class UserDaoImpl implements UserDao {
@Override
public void insert(Connection conn, UserEntity user) throws SQLException {
String sql = "insert into user_tbl(name,password,email) values (?,?,?)";
PreparedStatement ptmt = conn.prepareCall(sql);
//设置占位符参数,从1开始
ptmt.setString(1, user.getName());
ptmt.setString(2, user.getPassword());
ptmt.setString(3, user.getEmail());
//执行
ptmt.execute();
ptmt.close();
}
@Override
public void update(Connection conn, UserEntity user) throws SQLException {
String sql = "update user_tbl set name=?, password=?, email=? where id=?";
PreparedStatement ptmt = conn.prepareCall(sql);
//设置占位符参数,从1开始
ptmt.setString(1, user.getName());
ptmt.setString(2, user.getPassword());
ptmt.setString(3, user.getEmail());
ptmt.setLong(4, user.getId());
//执行
ptmt.execute();
ptmt.close();
}
@Override
public void delete(Connection conn, UserEntity user) throws SQLException {
String sql = "delete from user_tbl where id=?";
PreparedStatement ptmt = conn.prepareCall(sql);
//设置占位符参数,从1开始
ptmt.setLong(1, user.getId());
//执行
ptmt.execute();
ptmt.close();
}
}
编写测试类
package com.db.test;
import java.sql.Connection;
import java.sql.SQLException;
import com.db.dao.UserDao;
import com.db.dao.impl.UserDaoImpl;
import com.db.entity.UserEntity;
import com.db.util.ConnectionFactory;
public class UserDaoTest {
public static void main(String[] args) {
UserEntity user = new UserEntity();
user.setId(1);
user.setName("xixi");
user.setPassword("123456");
user.setEmail("1234@qq.com");
Connection conn = null;
try {
conn = ConnectionFactory.getInstance().getConnection();
conn.setAutoCommit(false);
UserDao userDao = new UserDaoImpl();
userDao.update(conn, user);
//userDao.delete(conn, user);
//userDao.insert(conn, user);
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
优化后项目包结构