1,错误用法一: if (name == "") { //do something…
January 22, 2018
JAVA: JDBC-Mysql操作类
package com.yunti.utils; 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; public class JdbcUtils { private final static String URL = "jdbc:mysql://127.0.0.1:3306/network"; private final static String USER = "root"; private final static String PWD = ""; private final static String Driver = "com.mysql.jdbc.Driver"; private Connection connection; private PreparedStatement prepareStatement; private ResultSet resultSet; public JdbcUtils() { try { Class.forName(Driver); connection = DriverManager.getConnection(URL,USER,PWD); } catch (Exception e) { e.printStackTrace(); } } /** * 多条查询 * @param sql * @param params * @return * @throws SQLException */ public List<Map<String,Object>> findMoreByStatement(String sql,List<Object> params) throws SQLException{ List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); prepareStatement = connection.prepareStatement(sql); if(params!=null&&!params.isEmpty()){ for(int i=0;i<params.size();i++){ prepareStatement.setObject(i+1, params.get(i)); } } resultSet = prepareStatement.executeQuery(); ResultSetMetaData rsmd = resultSet.getMetaData(); while(resultSet.next()){ Map<String,Object> map = new HashMap<String,Object>(); for(int i=1;i<=rsmd.getColumnCount();i++){ String column_name = rsmd.getColumnName(i); Object column_value = resultSet.getObject(column_name); if(column_value == null){ column_value = ""; } map.put(column_name, column_value); } list.add(map); } return list; } /** * 单条查询 * @param sql * @param params * @return * @throws SQLException */ public Map<String,Object> findByStatement(String sql,List<Object> params) throws SQLException{ Map<String,Object> map = new HashMap<String,Object>(); prepareStatement = connection.prepareStatement(sql); if(params!=null&&!params.isEmpty()){ for(int i=0;i<params.size();i++){ prepareStatement.setObject(i+1, params.get(i)); } } resultSet = prepareStatement.executeQuery(); ResultSetMetaData rsmd = resultSet.getMetaData(); while(resultSet.next()){ for(int i=1;i<=rsmd.getColumnCount();i++){ String column_name = rsmd.getColumnName(i); Object column_value = resultSet.getObject(column_name); if(column_value == null){ column_value = ""; } map.put(column_name, column_value); } } return map; } /** * 反射单条查询 * @param sql * @param params * @param c * @return * @throws Exception */ public <T> T findByStatement(String sql,List<Object> params,Class<T> c) throws Exception{ T resultObj = null; prepareStatement = connection.prepareStatement(sql); if(params!=null&&!params.isEmpty()){ for(int i=0;i<params.size();i++){ prepareStatement.setObject(i+1, params.get(i)); } } resultSet = prepareStatement.executeQuery(); ResultSetMetaData rsmd = resultSet.getMetaData(); while(resultSet.next()){ resultObj = c.newInstance(); for(int i=1;i<=rsmd.getColumnCount();i++){ String column_name = rsmd.getColumnName(i); Object column_value = resultSet.getObject(column_name); if(column_value == null){ column_value = ""; } Field field = c.getDeclaredField(column_name); field.setAccessible(true); field.set(resultObj, column_value); } } return resultObj; } /** * 反射多条查询 * @param sql * @param params * @param c * @return * @throws Exception */ public <T> List<T> findMoreByStatement(String sql,List<Object> params,Class<T> c) throws Exception{ List<T> list = new ArrayList<T>(); prepareStatement = connection.prepareStatement(sql); if(params!=null&&!params.isEmpty()){ for(int i=0;i<params.size();i++){ prepareStatement.setObject(i+1, params.get(i)); } } resultSet = prepareStatement.executeQuery(); ResultSetMetaData rsmd = resultSet.getMetaData(); while(resultSet.next()){ T resultObj = c.newInstance(); for(int i=1;i<=rsmd.getColumnCount();i++){ String column_name = rsmd.getColumnName(i); Object column_value = resultSet.getObject(column_name); if(column_value == null){ column_value = ""; } Field field = c.getDeclaredField(column_name); field.setAccessible(true); field.set(resultObj, column_value); } list.add(resultObj); } return list; } /** * 数据增删改 * @param sql * @param params * @return * @throws SQLException */ public boolean updateByPrepareStatement(String sql,List<Object> params) throws SQLException{ boolean flag = false; int result = -1; prepareStatement = connection.prepareStatement(sql); if(params != null && !params.isEmpty()){ for (int i = 0; i < params.size(); i++) { prepareStatement.setObject(i+1, params.get(i)); } } result = prepareStatement.executeUpdate(); flag = result > 0 ? true : false; return flag; } /** * 资源释放 */ public void releaseConn(){ if(prepareStatement != null){ try { connection.close(); prepareStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
实例:
@Test public void TestDB() { Mysql mysql = new Mysql(); String sql = "select * from " + TABLE_USERS; logger.info(DB_URL); //List<Object> params = new ArrayList<>(); //params.add(1,"test"); try { List<Map<String, Object>> data = mysql.findMoreByStatement(sql, null); System.out.println(data); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { mysql.releaseConn(); } }
JAVA: JDBC入门教程(二) 通过JDBC进行简单的增删改查
JAVA:JDBC入门教程(三) PreparedStatement的使用