倒腾(jdbc更改版，包括dao和测试)

oracle 字段自动变成大写，如果用反射，需要将pojo的字段改为大写，或者全部小写，然后修改ReflectSetFieldValue方法

 

package com.duoduo.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Logger;

public class DBUtil {
	private static Connection connection;
	private static PreparedStatement pstmt;
	private static ResultSet resultSet;
	private static String password;
	private static String username;
	private static String driver;
	private static String url;
	static {
		try {
			init();
			register();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/***
	 * 注册驱动 *
	 **/
	private static void register() throws ClassNotFoundException {
		Class.forName(driver);
	}

	/***
	 * 初始化配置 *
	 **/
	private static void init() throws IOException {
		Properties properties = new Properties();
		FileInputStream in = new FileInputStream(
				"properties/database.properties");
		properties.load(in);
		in.close();
		driver = properties.getProperty("driver");
		username = properties.getProperty("username");
		password = properties.getProperty("passwrod");
		url = properties.getProperty("url");
		Logger logger = Logger.getLogger("com.duoduo.jdbc");
		logger.info(driver);
		logger.info(username);
		logger.info(password);
		logger.info(url);
	}

	/***
	 * 获得连接 *
	 **/
	public static Connection getConnection2() throws SQLException {
		// 确保资源已经释放
		clear();
		connection = DriverManager.getConnection(url, username, password);
		return connection;
	}

	/***
	 * 获得连接 *
	 **/
	public static void getConnection() throws SQLException {
		// 确保资源已经释放
		clear();
		connection = DriverManager.getConnection(url, username, password);
		// return connection;
	}

	/***
	 * 开始事务 *
	 **/
	public static void startTrans(Connection conn) throws SQLException {
		if (conn != null) {
			conn.setAutoCommit(false);
		}
	}

	public static void startTrans() throws SQLException {
		if (connection != null) {
			connection.setAutoCommit(false);
		}
	}

	/***
	 * 结束事务 *
	 * 
	 * @throws SQLException
	 **/
	public static void endTrans(Connection conn) throws SQLException {
		if (conn != null) {
			conn.commit();
		}
	}

	public static void endTrans() throws SQLException {
		if (connection != null) {
			connection.commit();
		}
	}

	/***
	 * 回滚事务 *
	 * 
	 * @throws SQLException
	 **/
	public static void rollbackTrans(Connection conn) throws SQLException {
		if (conn != null) {
			conn.rollback();
		}
	}

	/***
	 * 清空资源 *
	 * 
	 * @throws SQLException
	 **/
	public static void clear(Connection conn) throws SQLException {
		if (conn != null) {
			conn.close();
		}
	}

	/***
	 * 清空资源 *
	 **/
	public static void clear(Connection conn, PreparedStatement ps)
			throws SQLException {
		if (ps != null) {
			ps.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	/***
	 * 清空资源 *
	 **/
	public static void clear(Connection conn, PreparedStatement ps, ResultSet rs)
			throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (ps != null) {
			ps.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	public static void clear() throws SQLException {
		if (resultSet != null) {
			resultSet.close();
			resultSet = null;
		}
		if (pstmt != null) {
			pstmt.close();
			pstmt = null;
		}
		if (connection != null) {
			connection.close();
			connection = null;
		}
	}

	private static 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 static 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 static 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 static <T> void ReflectSetFieldValue(Object target, String column,
			Object value, Class<T> cls) throws Exception {
		Field field = cls.getDeclaredField(column);
		field.setAccessible(true);
		String valueString = "";
		if (value != null) {
			valueString = value.toString();
		}
		field.set(target, valueString);
	}

	// 反射的方式封装
	public static <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 = "";
				}

				ReflectSetFieldValue(resultObject, cols_name.toLowerCase(),
						cols_value, cls);
			}
		}
		return resultObject;
	}

	public static <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();
		int N = 0;
		while (resultSet.next()) {
			N++;
			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 = "";
				}
				ReflectSetFieldValue(resultObject, cols_name.toLowerCase(),
						cols_value, cls);
			}
			list.add(resultObject);
		}
		Logger logger = Logger.getLogger("com.duoduo.util");
		logger.info(N + "");
		return list;

	}

	public static 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;
	}
}

 

database.properties

driver=oracle.jdbc.driver.OracleDriver
username=neu
passwrod=oracle
url=jdbc\:oracle\:thin\:@127.0.0.1\:1521\:ORACLE

 

pojo

package com.duoduo.pojo;

public class Student {
	private String sno;
	private String sname;
	private String age;
	private String birthday;

	public Student() {
		super();
	}

	public Student(String sname, String age, String birthday) {
		super();
		this.sname = sname;
		this.age = age;
		this.birthday = birthday;
	}

	public String getSno() {
		return sno;
	}

	public void setSno(String sno) {
		this.sno = sno;
	}

	public String getSname() {
		return sname;
	}

	public void setSname(String sname) {
		this.sname = sname;
	}

	public String getAge() {
		return age;
	}

	public void setAge(String age) {
		this.age = age;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public String toString() {
		return "Student [age=" + age + ", birthday=" + birthday + ", sname="
				+ sname + ", sno=" + sno + "]";
	}

}

dao

package com.duoduo.dao;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import com.duoduo.idao.IDao;
import com.duoduo.pojo.Student;
import com.duoduo.util.DBUtil;

public class StudentDao implements IDao<Student> {
	public List<Student> getAll() {
		String sql = "select * from student";
		List<Student> list = null;
		try {
			DBUtil.getConnection();
			list = DBUtil.findMoreRefResultSet(sql, null, Student.class);
			DBUtil.clear();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {

			e.printStackTrace();
		}
		return list;
	}

	public Student get(String id) {
		Student obj = null;
		String sql = "select * from student where sno = ?";
		Object[] params = { id };
		try {
			DBUtil.getConnection();
			obj = DBUtil.findSimpleRefResult(sql, Arrays.asList(params),
					Student.class);
			DBUtil.clear();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}

	public boolean delete(String id) {
		boolean isSuccessed = false;
		String sql = "delete from student where sno = ?";
		Object[] params = { id };
		try {
			DBUtil.getConnection();
			DBUtil.startTrans();
			try {
				isSuccessed = DBUtil.updateByPreparedStatement(sql, Arrays
						.asList(params));
			} catch (SQLException e) {
				DBUtil.rollbackTrans();
			}
			DBUtil.endTrans();
			DBUtil.clear();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return isSuccessed;
	}

	public boolean save(Student obj) {
		boolean isSuccessed = false;
		String id = "select to_char(sysdate,'yyyymmddhh24miss')||lpad(student_seq.nextval,3,0) id from dual";
		String sql = "insert into student(sno,sname,age,birthday) values(?,?,?,to_date(?,'yyyy-mm-dd'))";
		Object[] params = new Object[4];
		params[1] = obj.getSname();
		params[2] = obj.getAge();
		params[3] = obj.getBirthday();
		try {
			DBUtil.getConnection();
			Map<String, Object> idMap = DBUtil.findSimpleResult(id, null);
			params[0] = idMap.get("ID");
			Logger logger = Logger.getLogger("com.duoduo.dao");
			logger.info(params[0].toString());
			DBUtil.startTrans();
			try {
				isSuccessed = DBUtil.updateByPreparedStatement(sql, Arrays
						.asList(params));
			} catch (SQLException e) {
				DBUtil.rollbackTrans();
			}
			DBUtil.endTrans();
			DBUtil.clear();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return isSuccessed;
	}

	public boolean modify(Student obj) {
		boolean isSuccessed = false;
		Student old = get(obj.getSno());
		String sql = "update student set sname = ?,age = ? ,birthday = to_date(?,'yyyy-mm-dd') where sno = ? ";
		Object[] params = { obj.getSname(), obj.getAge(), obj.getBirthday(),
				obj.getSno() };
		try {
			DBUtil.getConnection();
			DBUtil.startTrans();
			try {
				isSuccessed = DBUtil.updateByPreparedStatement(sql, Arrays
						.asList(params));
			} catch (SQLException e) {
				DBUtil.rollbackTrans();
			}
			DBUtil.endTrans();
			DBUtil.clear();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return isSuccessed;
	}
}

 

测试

package com.duoduo.test;

import com.duoduo.dao.StudentDao;
import com.duoduo.pojo.Student;
import com.duoduo.std.StdIn;
import com.duoduo.std.StdOut;

public class Test {
	public static void showMenu() {
		System.out.println("********************");
		System.out.println("1.查询所有学生信息");
		System.out.println("2.录入数据");
		System.out.println("3.查询具体id学生信息");
		System.out.println("4.删除id的学生信息");
		System.out.println("5.更新id的学生信息");
		System.out.println("0.退出");
		System.out.println("********************");
	}

	public static void run() {
		StudentDao dao = new StudentDao();
		while (true) {
			showMenu();
			int choice = StdIn.readInt();
			switch (choice) {
			case 1:
				// 获得学生信息，并显示所有学生信息
				StdOut.println(dao.getAll());
				break;
			case 2:
				StdOut.println("请输入学生姓名：");
				String name = StdIn.readString();
				StdOut.println("请输入学生年龄：");
				String age = StdIn.readString();
				StdOut.println("请输入学生生日：");
				String birthday = StdIn.readString();
				Student student = new Student(name, age, birthday);
				dao.insert(student);
				break;
			case 3:
				StdOut.println("请输入学生id：");
				String id = StdIn.readString();
				StdOut.println(dao.get(id));
				break;
			case 4:
				StdOut.println("请输入学生id：");
				String id4 = StdIn.readString();
				StdOut.println(dao.delete(id4));
				break;
			case 5:
				StdOut.println("请输入学生id：");
				String id5 = StdIn.readString();
				Student old = dao.get(id5);
				StdOut.println("请输入学生姓名：");
				String name5 = StdIn.readString();
				StdOut.println("请输入学生年龄：");
				String age5 = StdIn.readString();
				StdOut.println("请输入学生生日：");
				String birthday5 = StdIn.readString();
				old.setSname(name5);
				old.setAge(age5);
				old.setBirthday(birthday5);
				StdOut.println(dao.update(old));
				break;
			case 0:
				System.exit(0);
			}
		}
	}

	public static void main(String[] args) {
		run();
	}
}