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> executeQuery(String sql) {

try {

Connection conn = DBUtil.getConnection();

Statement st = conn.createStatement();

ResultSet set = st.executeQuery(sql);

ResultSetMetaData metaData = set.getMetaData();

List> result = new ArrayList>();

int columnCount = metaData.getColumnCount();

while (set.next()) {

Map map = new HashMap();

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 executeQuerySingle(String sql) {

List> 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 row = DBUtil.executeQuerySingle(countSql);

long totalRows = (Long) row.get("totalRows");

return totalRows;

}

public static PageBean> executePage(String sql,

long pageSize, long pageId) {

PageBean> result = new PageBean>();

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> list = DBUtil.executeQuery(sql);

result.setPageContent(list);

result.setPageId(pageId);