JDBC增删改查
package com.zjy.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
class DBUtil {
public static Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "密码";
Connection conn = DriverManager.getConnection(url, username,
password);
return conn;
} catch (Exception e) {
throw new IllegalArgumentException(e);
}
}
public static boolean execute(String sql) {
try {
Connection conn = DBUtil.getConnection();
Statement st = conn.createStatement();
boolean result = st.execute(sql);
st.close();
conn.close();
return result;
} catch (SQLException e) {
throw new IllegalArgumentException(e);
}
}
public static List<Map<String, Object>> executeQuery(String sql) {
try {
Connection conn = DBUtil.getConnection();
Statement st = conn.createStatement();
ResultSet set = st.executeQuery(sql);
ResultSetMetaData metaData = set.getMetaData();
List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
int columnCount = metaData.getColumnCount();
while (set.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
String name = metaData.getColumnName(i);
Object value = set.getObject(name);
map.put(name, value);
}
result.add(map);
}
set.close();
st.close();
conn.close();
return result;
} catch (SQLException e) {
throw new IllegalArgumentException(e);
}
}
public static Map<String, Object> executeQuerySingle(String sql) {
List<Map<String, Object>> list = executeQuery(sql);
return list.get(0);
}
public static long getTotalRows(String sql) {
int start = sql.indexOf("from");
String countSql = "select count(*) as totalRows "
+ sql.substring(start);
Map<String, Object> row = DBUtil.executeQuerySingle(countSql);
long totalRows = (Long) row.get("totalRows");
return totalRows;
}
public static PageBean<Map<String, Object>> executePage(String sql,
long pageSize, long pageId) {
PageBean<Map<String, Object>> result = new PageBean<Map<String, Object>>();
long count = getTotalRows(sql);
long pageTotal = count % pageSize == 0 ? count / pageSize : count
/ pageSize + 1;// 修正分页总数
pageId = pageId > pageTotal ? pageTotal : pageId;// 修正分页编号
long start = (pageId - 1) * pageSize;// 计算开始位置
long end = pageId * pageSize;
end = end > count ? count : end;// 结算结束位置
sql = sql + " limit " + start + "," + pageSize;
List<Map<String, Object>> list = DBUtil.executeQuery(sql);
补充：软件开发 , Java ,
