倒腾(jdbc)

最新推荐文章于 2022-08-02 02:52:07 发布

d8282048

最新推荐文章于 2022-08-02 02:52:07 发布

阅读量438

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/d8282048/article/details/9626837

DBManager

package com.sinaapp.skbanji.db.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.sql.ResultSetMetaData;

import javax.naming.NamingException;

public class DBManager {
	private IConnection iConnection;
	private Connection connection;
	private PreparedStatement pstmt;
	private ResultSet resultSet;

	public DBManager() {
		iConnection = new TomcatPoolConnection();
		//iConnection = new DriverManagerConnection();
	}

	public void setConnection(IConnection iConnection){
		this.iConnection = iConnection;
	}
	public Connection getConnection() throws NamingException {
		try {
			connection = iConnection.getConnection();
		} catch (Exception e) {

			e.printStackTrace();
		}
		return connection;
	}

	public void releasePreparedStatement() throws SQLException {
		if (pstmt != null) {
			pstmt.close();
		}
	}

	public void releaseResultSet() throws SQLException {
		if (resultSet != null) {
			resultSet.close();
		}
	}

	public boolean releaseConnection(Connection connection) throws SQLException {
		return iConnection.releaseConnection(connection);
	}

	public void releaseConnection() throws SQLException {
		releaseResultSet();
		releasePreparedStatement();
		releaseConnection(connection);
	}

	private void setParamsToPreparedStatement(PreparedStatement pstmt,
			List<Object> params) throws SQLException {
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
	}

	public boolean updateByPreparedStatement(String sql, List<Object> params)
			throws SQLException {
		boolean flag = false;
		// result:当用户执行添加删除修改时所影响数据库的行数
		int result = -1;

		pstmt = connection.prepareStatement(sql);
		setParamsToPreparedStatement(pstmt, params);
		result = pstmt.executeUpdate();
		flag = result > 0 ? true : false;
		return flag;
	}

	public Map<String, Object> findSimpleResult(String sql, List<Object> params)
			throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		// index:占位符地址
		pstmt = connection.prepareStatement(sql);
		setParamsToPreparedStatement(pstmt, params);
		resultSet = pstmt.executeQuery();

		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
		}

		return map;

	}

	public List<Map<String, Object>> findMoreResultSet(String sql,
			List<Object> params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		pstmt = connection.prepareStatement(sql);
		setParamsToPreparedStatement(pstmt, params);
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();

		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}
		return list;

	}

	private <T> void ReflectSetFieldVale(Object target, String column,
			Object value, Class<T> cls) throws Exception {
		Field field = cls.getDeclaredField(column);
		field.setAccessible(true);
		field.set(target, value);
	}

	// 反射的方式封装
	public <T> T findSimpleRefResult(String sql, List<Object> params,
			Class<T> cls) throws Exception {
		T resultObject = null;

		pstmt = connection.prepareStatement(sql);
		setParamsToPreparedStatement(pstmt, params);
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();

		while (resultSet.next()) {
			resultObject = cls.newInstance();
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				// Field field = cls.getDeclaredField(cols_name);
				// field.setAccessible(true);
				// field.set(resultObject, cols_value);
				ReflectSetFieldVale(resultObject, cols_name, cols_value, cls);
			}
		}
		return resultObject;
	}

	public <T> List<T> findMoreRefResultSet(String sql, List<Object> params,
			Class<T> cls) throws Exception {
		List<T> list = new ArrayList<T>();

		pstmt = connection.prepareStatement(sql);
		setParamsToPreparedStatement(pstmt, params);
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();

		while (resultSet.next()) {
			T resultObject = cls.newInstance();
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				ReflectSetFieldVale(resultObject, cols_name, cols_value, cls);
			}
			list.add(resultObject);
		}
		return list;

	}

}

IConnection

package com.sinaapp.skbanji.db.util;

import java.sql.Connection;

public interface IConnection {
	public Connection getConnection();
	public boolean releaseConnection(Connection connection);
}

TomcatPoolConnection

package com.sinaapp.skbanji.db.util;

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

import javax.naming.InitialContext;
import javax.sql.DataSource;

public class TomcatPoolConnection implements IConnection {

	public TomcatPoolConnection() {
		// TODO Auto-generated constructor stub
	}

	@Override
	public Connection getConnection() {
		InitialContext ic;
		
		Connection connection = null;
		try {
			ic = new InitialContext();
			
			DataSource ds = (DataSource) ic.lookup("java:/comp/env/jdbc/skbanjiDB");
			connection = ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}

	@Override
	public boolean releaseConnection(Connection connection) {
		boolean flag = false;
		if (connection != null) {
			try {
				connection.close();
				flag = true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return flag;
	}

}

DriverManagerConnection

package com.sinaapp.skbanji.db.util;

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

public class DriverManagerConnection implements IConnection {
	private final String DRIVER = "com.mysql.jdbc.Driver";
	private final String URL = "jdbc:mysql://127.0.0.1:3306/skbanji";
	private final String PASSWORD = "duoduo";
	private final String USER = "root";

	@Override
	public Connection getConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return connection;
	}

	public DriverManagerConnection() {
		try {
			Class.forName(DRIVER);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean releaseConnection(Connection connection) {
		boolean flag = false;
		if (connection != null) {
			try {
				connection.close();
				flag = true;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

}

MajorDao

package com.sinaapp.skbanji.banji.dao;

import java.util.ArrayList;
import java.util.List;

import com.sinaapp.skbanji.banji.model.Major;
import com.sinaapp.skbanji.db.util.DBManager;

public class MajorDao {
	private DBManager dbManager = null;

	public MajorDao() {
		dbManager = new DBManager();
	}

	public void setDbManager(DBManager dbManager) {
		this.dbManager = dbManager;
	}
	public Major getMajor(int college_id,String major_name){
		return getMajor(Integer.toString(college_id), major_name);
	}
	
	public Major getMajor(String college_id, String major_name) {
		List<Major> list = null;

		try {
			dbManager.getConnection();
			String sql = "select * from major where college_id = ? and name = ?";
			List<Object> params = new ArrayList<Object>();
			params.add(college_id);
			params.add(major_name);

			list = dbManager.findMoreRefResultSet(sql, params, Major.class);

			dbManager.releaseConnection();
			if (list.size() > 0) {
				return list.get(0);
			}
		} catch (Exception e) {

			e.printStackTrace();
		}
		return null;
	}
	public List<Major> getMajors(int college_id){
		return getMajors(Integer.toString(college_id));
	}
	public List<Major> getMajors(String college_id) {
		List<Major> list = null;
		try {
			dbManager.getConnection();
			String sql = "SELECT * FROM  major WHERE college_id = ?";
			List<Object> params = new ArrayList<Object>();
			params.add(college_id);
			list = dbManager.findMoreRefResultSet(sql, params, Major.class);
			dbManager.releaseConnection();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list;
	}
	public void save(Major major){
		save(major.getCollege_id(),major.getName());
	}
	public void save(int college_id,String major_name){
		save(Integer.toString(college_id),major_name);
	}
	public void save(String college_id,String major_name){
		try {
			dbManager.getConnection();
			String sql = "INSERT INTO  major (name,college_id) VALUES (?,?)";
			List<Object> params = new ArrayList<Object>();

			params.add(major_name);
			params.add(college_id);

			dbManager.updateByPreparedStatement(sql, params);
			dbManager.releaseConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}