JDBC的业务逻辑的应用

文件的定义规范：

 

 

Dao.java文件内容：

package com.sk.jdbc.dao;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.sk.jdbc.util.ConnectionUtil;

//Dao类中存放通用的数据访问方法
public class Dao<T> {

	private Class<T> clazz;
	private QueryRunner queryRunner = new QueryRunner();

	public Connection conn = null;

	@SuppressWarnings("unchecked")
	public Dao() {
		Type type = this.getClass().getGenericSuperclass(); //
		if (type instanceof ParameterizedType) {
			ParameterizedType parameterizedType = (ParameterizedType) type;
			Type[] types = parameterizedType.getActualTypeArguments();
			if (types != null && types.length > 0) {
				if (types[0] instanceof Class) {
					clazz = (Class<T>) types[0];
				}
			}
		}
	}

	public void openConnection() throws SQLException {
		if (this.conn == null  || this.conn.isClosed()) {
			this.conn = ConnectionUtil.getConnection();
		}
	}

	public void closeConnection() throws SQLException {
		if (this.conn != null &&  !this.conn.isClosed() ) {
			ConnectionUtil.release(this.conn);
		}
	}

	public void beginTransaction() throws SQLException {
			openConnection();
			this.conn.setAutoCommit(false);
	}

	public void commit() throws SQLException {
		if (this.conn != null) {
			this.conn.commit();
		}
	}

	public void rollback() throws SQLException {
		if (this.conn != null) {
			this.conn.rollback();
		}
	}

	// 通用的update()方法：
	public void update(String sql, Object... args) throws SQLException {
		openConnection();
		queryRunner.update(conn, sql, args);
	}

	// 通用的查询方法----查单个记录
	public T queryOne(String sql, Object... args) throws SQLException {

		T entity = null;
		openConnection();
		entity = queryRunner.query(conn, sql, new BeanHandler<>(clazz), args);
		return entity;

	}

	// 通用的查询方法----查多个记录
	public List<T> queryList(String sql, Object... args) throws SQLException {

		List<T> list = null;
		openConnection();
		list = queryRunner.query(conn, sql, new BeanListHandler<>(clazz), args);
		return list;

	}

	// 查单值
	public Object queryValue(String sql, Object... args) throws SQLException {

		Object result = null;
		openConnection();
		result = queryRunner.query(conn, sql, new ScalarHandler<>(), args);
		return result;
	}

	public Map<String, Object> queryMap(String sql, Object... args)
			throws SQLException {
		Map<String, Object> phoneMap = null;
		openConnection();
		phoneMap = queryRunner.query(conn, sql, new MapHandler(), args);
		return phoneMap;

	}

	public List<Map<String, Object>> queryMapList(String sql, Object... args)
			throws SQLException {
		List<Map<String, Object>> phoneMapList = null;
		openConnection();
		phoneMapList = queryRunner.query(conn, sql, new MapListHandler(), args);
		return phoneMapList;

	}

}

　　

 UserDao.java 文件：

package com.sk.jdbc.dao;

import java.sql.SQLException;

import com.sk.jdbc.entity.User;


public class UserDao extends Dao<User> {
	
	public void updateAccount(int id,double amount) throws SQLException{
		String sql="update t_user set account=account+? where id=?";
		update(sql, amount,id);
		
		
	}

}

　　

User.java文件

package com.sk.jdbc.entity;

//ORM
public class User {
	
	private Integer id;
	private String username;
	private String password;
	private int role;
	private Double account;
	
	public User() {
		// TODO Auto-generated constructor stub
	}
	
	

	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 int getRole() {
		return role;
	}

	public void setRole(int role) {
		this.role = role;
	}

	public Double getAccount() {
		return account;
	}

	public void setAccount(Double account) {
		this.account = account;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password="
				+ password + ", role=" + role + ", account=" + account + "]";
	}
	
	

}

　　

UserService.java文件

package com.sk.jdbc.service;

import java.sql.SQLException;

import com.sk.jdbc.dao.UserDao;

public class UserService {

	public void transfer(int outId, int inId, double amount)
			throws SQLException {
		UserDao userDao = new UserDao();
		try {
			userDao.beginTransaction(); // 开启事务
			userDao.updateAccount(outId, -amount);
//			int i=100/0;
//			System.out.println(i);
			userDao.updateAccount(inId, amount);
			userDao.commit(); // 提交事务
		} catch (SQLException e) {
			userDao.rollback(); // 回滚事务
			e.printStackTrace();
			throw e;
		} finally {
			userDao.closeConnection();
		}
	}

}

　　

ConnetcionUtil.java文件

package com.sk.jdbc.util;

import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class ConnectionUtil {

	private static ComboPooledDataSource ds=null;
	static {
		
		ds = new ComboPooledDataSource("mysqlc3p0");
	}
	
	
	public static Connection getConnection() throws SQLException {
		return ds.getConnection();


	}

	public static void release(	Connection conn) throws SQLException {
			conn.close();

	}

}

　　

c3p0-config.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

	<named-config name="mysqlc3p0">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/userdb?characterEncoding=utf8
		</property>
		<property name="user">root</property>
		<property name="password">1234</property>
		<property name="initialPoolSize">10</property>
		<property name="maxPoolSize">50</property>
		<property name="minPoolSize">10</property>
		<property name="maxStatements">0</property>
		<property name="acquireIncrement">5</property>
		<property name="maxStatementsPerConnection">10</property>
	</named-config>
	
	<named-config name="orclc3p0">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/stuinfodb?characterEncoding=utf8
		</property>
		<property name="user">lal</property>
		<property name="password">root</property>
		<property name="initialPoolSize">10</property>
		<property name="maxPoolSize">50</property>
		<property name="minPoolSize">10</property>
		<property name="maxStatements">0</property>
		<property name="acquireIncrement">5</property>
		<property name="maxStatementsPerConnection">10</property>
	</named-config>
</c3p0-config>

　　

TransferAccountTest.java文件

package com.sk.jdbc.test;

import java.sql.SQLException;

import com.sk.jdbc.service.UserService;

public class TransferAccountTest {
	
    public static void main(String[] args)  {
    	UserService userService=new UserService();
    	try {
			userService.transfer(1,2,1000);
			System.out.println("转账成功!");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("转账失败!");
		}
		
	}		

	

}

　　

sql.sql文件

select * from t_course where c_no='450101';

SELECT c_no,c_name,c_time FROM t_course WHERE c_time=(select max(c_time) from t_course); 

select s_id,s_no,s_name,s_birth,s_gender,s_class,s_phone from t_student where s_id=1;

select count(s_no) males from t_student where s_gender='M';

select r_result from t_result where r_s_no='18302012301' and r_c_no='450101';

　　

数据库样式：