一、使用properties配置文件进行mysql数据库连接
1、mysql数据库的配置文件：
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8
username=root
password=a123456
2、加载配置文件，封装好的获取数据库连接、手动开启关闭事务、事务回滚以及数据库关闭的方法
public class JDBCUtils {
private static Properties properties = new Properties();
private static ThreadLocal<Connection> tl = new ThreadLocal<>(); //本地化线程 在线程中创建一个map
static {
//这种必须放在项目路径下
// try {
// properties.load(new FileInputStream("jdbc.properties"));
// } catch (IOException e) {
// e.printStackTrace();
// }
//一般使用这种 在任何路径下都可以加载配置文件
try {
properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
Class.forName(properties.getProperty("driverClass"));
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//连接数据库的方法
public static Connection getConnection(){
Connection connection = tl.get();
try {
// connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
// connection.setAutoCommit(false);//开启事务 由自动变为手动
if (connection == null){
connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
tl.set(connection);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return tl.get();
}
public static void begin(){
Connection connection = getConnection();
try {
//开启事务
connection.setAutoCommit(false);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void commit(){
try {
// connection.commit();
getConnection().commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(getConnection(),null,null);
}
}
public static void rollback(){
try {
getConnection().rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(getConnection(),null,null);
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet!= null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement !=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!= null){
try {
if (tl.get()!=null){
//关闭连接前先删除存储在集合中的连接
tl.remove();
}
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
3、封装好的增删改 查一个查多个的方法
import com.dragon.utils.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BaseDao<E> {
/**
* 通用的增删改方法
* @param sql
* @param objects
* @return
*/
public boolean updateDB(String sql,Object...objects){
Connection connection = null;
PreparedStatement statement = null;
try{
connection = JDBCUtils.getConnection();
System.out.println("1="+connection);
statement = connection.prepareStatement(sql);
if(objects != null){
for(int i = 0; i < objects.length; i++){
statement.setObject(i+1,objects[i]);
}
}
int result = statement.executeUpdate();
if(result > 0){
return true;
}
}catch (Exception e){
e.printStackTrace();
}finally {
//connection 必须为null
JDBCUtils.close(null,statement,null);
}
return false;
}
public E getOne(Class clazz,String sql,Object...objects){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try{
connection = JDBCUtils.getConnection();
statement = connection.prepareStatement(sql);
if(objects != null){
for(int i = 0; i < objects.length; i++){
statement.setObject(i+1,objects[i]);
}
}
resultSet = statement.executeQuery();
//获取所查询的表的所有信息
ResultSetMetaData metaData = resultSet.getMetaData();
//查了多少列
int count = metaData.getColumnCount();
while(resultSet.next()) {
//利用反射调用无参构造创建对象 也可用有参构造 但是得先查构造器有哪些 然后再通过构造器去选有参构造
E e = (E) clazz.newInstance();
for (int i = 0; i < count; i++) {
String colLab = metaData.getColumnLabel(i + 1);//获取列名
Object value = resultSet.getObject(colLab);//通过列名获取列值
Field field = clazz.getDeclaredField(colLab);//通过class获取列名对应的属性名
field.setAccessible(true);//对私有属性赋值，需提升权限
field.set(e, value);//对属性赋值
}
return e;
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.close(connection,statement,resultSet);
}
return null;
}
public List<E> getAll(Class clazz,String sql,Object...objects){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
statement= connection.prepareStatement(sql);
resultSet = statement.executeQuery();
//新建一个集合 ; 将结果集里面的数据, 封装到这个集合里面去
List<E> list = new ArrayList<E>();
ResultSetMetaData metaData = resultSet.getMetaData();
int count = metaData.getColumnCount();
while(resultSet.next()) {
E e = (E) clazz.newInstance();
for (int i = 0; i < count; i++) {
String colLab = metaData.getColumnLabel(i + 1);//获取列名
Object value = resultSet.getObject(colLab);//通过列名获取列值
Field field = clazz.getDeclaredField(colLab);//通过class获取列名对应的属性名
field.setAccessible(true);//对私有属性赋值，需提升权限
field.set(e, value);//对属性赋值
}
list.add(e);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
二、单线程转账业务
1、实体类entity（与数据库表对应）public class User {
//列中的列可以不一一对应，但只能多不能少
private Integer id;
private String username;
private String password;
private Integer account;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getAccount() {
return account;
}
public void setAccount(Integer account) {
this.account = account;
}
public User() {
}
public User(Integer id) {
this.id = id;
}
public User(Integer id, String username, String password, Integer account) {
this.id = id;
this.username = username;
this.password = password;
this.account = account;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", account=" + account +
'}';
}
}
2、service接口
public interface AccountService {
boolean transfer(User user1,User user2);
}
3、service实现类
import com.dragon.dao.UserDao;
import com.dragon.dao.UserDaoImpl;
import com.dragon.entity.User;
import com.dragon.utils.JDBCUtils;
public class AccountServiceImpl implements AccountService {
@Override
public boolean transfer(User user1, User user2) {
JDBCUtils.begin();
//需要事务就begin 不需要就直接getConnection
// Connection connection = JDBCUtils.getConnection();
try {
UserDao userDao = new UserDaoImpl();
boolean res1 = userDao.updateUser("update user set account = account -? where id = ?", user1.getAccount(), user1.getId());
// int i = 10/0;
boolean res2 = userDao.updateUser("update user set account = account +? where id = ?", user1.getAccount(), user2.getId());
if (res1 && res2){
//提交
JDBCUtils.commit();
System.out.println("commit");
return true;
}
}catch (Exception e){
System.out.println("123");
e.printStackTrace();
//回滚
JDBCUtils.rollback();
}
return false;
}
}
4、测试类
public class TestAccount {
public static void main(String[] args) {
AccountService accountService = new AccountServiceImpl();
User user1 = new User(2, null, null, 200);
User user2 = new User(1, null, null, 0);
if(accountService.transfer(user1,user2)){
System.out.println("转账成功");
}else {
System.out.println("转账失败");
}
}
}
浙公网安备 33010602011771号