一个简单的SQL语句拼装代码

最新推荐文章于 2021-01-19 02:24:58 发布

daditao

最新推荐文章于 2021-01-19 02:24:58 发布

阅读量649

点赞数

分类专栏： java

java 专栏收录该内容

44 篇文章 0 订阅

订阅专栏

http://blog.csdn.net/njchenyi/article/details/6691790

本人很懒，非常不想写那些SQL语句，特别是要和列名和值的顺序要一一对应，万一出点错查起来很困难。反正对性能没啥很高要求，那就随手写了个自己拼装SQL语句的代码。

用的c3p0数据库连接池，只完成了单表的增删改查操作，不支持多表操作。

插入操作接口add(String tableName, HashMap<String, String> values)。tableName就是要做操作的表名，values是插入时需要设置的值，采用名值对方式放入Map中。
删除操作接口delete(String tableName, HashMap<String, String> conditions)。tableName就是要做操作的表名，conditions是删除时对应的条件，采用名值对方式放入Map中。
更新操作接口update(String tableName, HashMap<String, String> values, HashMap<String, String> conditions)。tableName就是要做操作的表名，values是更新时需要设置的值，采用名值对方式放入Map中，conditions是更新时对应的条件，采用名值对方式放入Map中。
查询操作接口select(String tableName, HashMap<String, String> conditions)。tableName就是要做操作的表名，conditions是查询时对应的条件，采用名值对方式放入Map中。

[java] view plain copy

import java.sql.*;
import java.util.*;
/**
*
* @author Chen Yi <njchenyi@gmail.com>
*/
public class DatabaseOperate {
// String dirverClass = "com.mysql.jdbc.Driver";
// String url = "jdbc:mysql://10.45.7.23:3306/gztr_ema?autoReconnect=true";
// String username = "root";
// String password = "rootroot";
// int minPoolSize = 3;
// int maxPoolSize = 15;
public ConnectionManager cm = null;
// /**
// *
// * @throws Exception
// */
// public DatabaseOperate() throws Exception {
// cm = ConnectionManager.createConnectionManager(dirverClass, url, username, password, maxPoolSize, minPoolSize);
// }
/**
*
* @param dirverClass
* @param url
* @param username
* @param password
* @param maxPoolSize
* @param minPoolSize
* @throws Exception
*/
public DatabaseOperate(String dirverClass, String url, String username, String password, int maxPoolSize, int minPoolSize) throws Exception {
cm = ConnectionManager.createConnectionManager(dirverClass, url, username, password, maxPoolSize, minPoolSize);
}
/**
* 增加记录
* @param tableName
* @param values
* @return int 插入后返回插入行的记录
* @throws SQLException
*/
public int add(String tableName, HashMap<String, String> values) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
int index = -1;
try {
StringBuilder sql = new StringBuilder();
StringBuilder column = new StringBuilder();
StringBuilder data = new StringBuilder();
if ((values != null) && !values.isEmpty()) {
Set<String> input = values.keySet();
Iterator<String> it = input.iterator();
for (; it.hasNext();) {
String s = it.next();
column.append(s);
// data.append("'");
data.append(values.get(s));
if (it.hasNext()) {
column.append(", ");
data.append(" , ");
}
}
} else {
throw new SQLException(" values can not be empty or null");
}
sql.append("INSERT INTO ");
sql.append(tableName);
sql.append(" (");
sql.append(column);
sql.append(") VALUES ( ");
sql.append(data);
sql.append(")");
System.out.println(sql.toString());
conn = cm.getConnection();
stmt = conn.createStatement();
stmt.execute(sql.toString());
rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
if (rs.next()) {
index = rs.getInt(1);
}
return index;
} catch (SQLException e) {
// e.printStackTrace();
throw e;
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
}
}
}
}
/**
* 删除记录
* @param tableName
* @param conditions
* @throws SQLException
*/
public void delete(String tableName, HashMap<String, String> conditions) throws SQLException {
Connection conn = null;
Statement stmt = null;
try {
StringBuilder sql = new StringBuilder();
sql.append("DELETE FROM ");
sql.append(tableName);
if ((conditions != null) && !conditions.isEmpty()) {
Set<String> input = conditions.keySet();
Iterator<String> it = input.iterator();
StringBuilder condition = new StringBuilder();
for (; it.hasNext();) {
String s = it.next();
condition.append(s);
condition.append(" = ");
condition.append(conditions.get(s));
if (it.hasNext()) {
condition.append(" AND ");
}
}
sql.append(" WHERE ");
sql.append(condition);
} else {
throw new SQLException(" conditions can not be empty or null");
}
System.out.println(sql.toString());
conn = cm.getConnection();
stmt = conn.createStatement();
stmt.execute(sql.toString());
} catch (SQLException e) {
// e.printStackTrace();
throw e;
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
}
}
}
}
/**
* 更新记录
* @param tableName
* @param values
* @param conditions
* @throws SQLException
*/
public void update(String tableName, HashMap<String, String> values, HashMap<String, String> conditions) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
StringBuilder sql = new StringBuilder();
sql.append("UPDATE ");
sql.append(tableName);
sql.append(" SET ");
if ((values != null) && !values.isEmpty()) {
Set<String> input = values.keySet();
Iterator<String> it = input.iterator();
StringBuilder data = new StringBuilder();
for (; it.hasNext();) {
String s = it.next();
data.append(s + " = " + values.get(s));
if (it.hasNext()) {
data.append(",");
}
}
sql.append(data);
} else {
throw new SQLException(" values can not be empty or null");
}
if (((conditions != null) && !conditions.isEmpty())) {
Set<String> input = conditions.keySet();
Iterator<String> it = input.iterator();
StringBuilder condition = new StringBuilder();
for (; it.hasNext();) {
String s = it.next();
condition.append(s);
condition.append(" = ");
condition.append(conditions.get(s));
if (it.hasNext()) {
condition.append(" AND ");
}
}
sql.append(" WHERE ");
sql.append(condition);
} else {
throw new SQLException(" conditions can not be empty or null");
}
System.out.println(sql.toString());
conn = cm.getConnection();
stmt = conn.createStatement();
stmt.execute(sql.toString());
} catch (SQLException e) {
throw e;
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
}
}
}
}
/**
* 查询记录
* @param tableName
* @param conditions
* @return
* @throws SQLException
*/
public Results select(String tableName, HashMap<String, String> conditions) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM ");
sql.append(tableName);
if ((conditions != null) && !conditions.isEmpty()) {
Set<String> input = conditions.keySet();
Iterator<String> it = input.iterator();
StringBuilder condition = new StringBuilder();
for (; it.hasNext();) {
String s = it.next();
condition.append(s);
condition.append(" = ");
condition.append(conditions.get(s));
if (it.hasNext()) {
condition.append(" AND ");
}
}
sql.append(" WHERE ");
sql.append(condition);
} else {
}
System.out.println(sql.toString());
conn = cm.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql.toString());
Results results = new Results();
ResultSetMetaData rsm = rs.getMetaData(); //获得列集
int col = rsm.getColumnCount(); //获得列的个数
String colName[] = new String[col];
//取结果集中的表头名称, 放在colName数组中
for (int i = 0; i < col; i++) { //-->第一列,从1开始.所以获取列名,或列值,都是从1开始
colName[i] = rsm.getColumnName(i + 1); //-->获得列值的方式一:通过其序号
// System.out.println(colName[i]);
}//End for
rs.last();
int row = rs.getRow();
rs.beforeFirst();
String data[][] = new String[row][col];
//取结果集中的数据, 放在data数组中
for (int i = 0; i < row; i++) {
rs.next();
for (int j = 0; j < col; j++) {
data[i][j] = rs.getString(j + 1);
// System.out.print(data[i][j]);
}
// System.out.println();
}//End for
results.setColumnName(colName);
results.setData(data);
// System.out.println(results.getDataRows());
return results;
} catch (SQLException e) {
// e.printStackTrace();
throw e;
} finally {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
}
}
}
}
// public static void main(String[] arg) {
// try {
// DatabaseOperate dl = new DatabaseOperate();
// HashMap<String, String> values = new HashMap<String, String>();
// values.put("coId", "1");
dl.add("TMF.T_APP_Car",in);
// HashMap<String, String> conditions = new HashMap<String, String>();
// conditions.put("userCode", "'admin'");
// conditions.put("coId", "'2'");
dl.select("t_app_user", conditions);
// dl.update("t_app_user", values, conditions);
// // System.out.println(rs.getRow());
// } catch (Exception ex) {
// ex.printStackTrace();
// }
// }
public void close() {
cm = null;
}
}

为了供多个模块共用，所以在最后close方法中没有调用cm.close()方法，这个根据需要自己添加吧。

main函数是我自己做简单测试用的，也可以当做例子。

另外还有个辅助类用于查询时使用

[java] view plain copy

/**
*
* @author Chen Yi <njchenyi@gmail.com>
*/
public class Results {
private String[] columnName = null;
private String[][] data = null;
int cursor = -1;
public int findColumnIndex(String name) {
for (int i = 0; i < columnName.length; i++) {
if (name.equals(columnName[i])) {
return i;
}
}
return -1;
}
public int getDataRows() {
return data.length;
}
public String getString(String columnName) throws Exception {
int i = findColumnIndex(columnName);
if (i < 0) {
throw new Exception("Can not find column name: " + columnName);
}
return data[cursor][i];
}
public boolean next() {
if (data.length - cursor > 1) {
cursor++;
// System.out.println("cursor at: "+cursor);
// if(cursor>=data.length){
// cursor = -1;
// }
return true;
} else {
return false;
}
}
public void beforeFirst() {
cursor = -1;
}
public void afterLast() {
cursor = data.length;
}
public void clear() {
cursor = -1;
columnName = null;
data = null;
}
/**
* @return the columnName
*/
public String[] getColumnName() {
return columnName;
}
/**
* @param columnName the columnName to set
*/
public void setColumnName(String[] columnName) {
this.columnName = columnName;
}
/**
* @return the data
*/
public String[][] getData() {
return data;
}
/**
* @param data the data to set
*/
public void setData(String[][] data) {
this.data = data;
}
}

daditao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一个简单的SQL语句拼装代码

http://blog.csdn.net/njchenyi/article/details/6691790本人很懒，非常不想写那些SQL语句，特别是要和列名和值的顺序要一一对应，万一出点错查起来很困难。反正对性能没啥很高要求，那就随手写了个自己拼装SQL语句的代码。用的c3p0数据库连接池，只完成了单表的增删改查操作，不支持多表操作。插入操作接口add(Stri
复制链接

扫一扫

专栏目录