倒腾(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.OracleDriverusername=neupasswrod=oracleurl=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();}}