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 = "64419024";
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);
result.setPageContent(list);
result.setPageId(pageId);
result.setPageTotal(pageTotal);
return result;
}
}

class Customer {
private int id;
private String username;
private String password;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public static Customer fillMap(Map<String, Object> values) {
Customer c = new Customer();
c.setId((Integer) values.get("id"));
c.setUsername((String) values.get("username"));
c.setPassword((String) values.get("password"));
return c;
}

public static List<Customer> fillList(List<Map<String, Object>> values) {
List<Customer> result = new ArrayList<Customer>();
for (int i = 0; i < values.size(); i++) {
result.add(fillMap(values.get(i)));
}
return result;
}
public static String makeSql(Customer c){
StringBuilder sql=new StringBuilder();
boolean whereExists=false;
sql.append("select * from customer ");
if(c.getId()>0){
sql.append(whereExists?(" or id="+c.getId()):" where id="+c.getId());
if(!whereExists){whereExists=true;}
}
String username=c.getUsername();
if(username!=null&&!username.equals("")){
sql.append(whereExists?(" or username like '%"+username)+"%'":" where username like '%"+username+"%'");
if(!whereExists){whereExists=true;}
}
String password=c.getPassword();
if(password!=null&&!password.equals("")){
sql.append(whereExists?(" or password like '%"+password+"%'"):" where password like '%"+password+"%'");
if(!whereExists){whereExists=true;}
}
return sql.toString();
}
}

class ExistsStatus<T> {
private boolean exists;
private T t;

public boolean isExists() {
return exists;
}

public void setExists(boolean exists) {
this.exists = exists;
}

public T getT() {
return t;
}

public void setT(T t) {
this.t = t;
}
}

class PageBean<T> {
private List<T> pageContent;
private long pageId;
private long pageTotal;

public List<T> getPageContent() {
return pageContent;
}

public void setPageContent(List<T> pageContent) {
this.pageContent = pageContent;
}

public long getPageId() {
return pageId;
}

public void setPageId(long pageId) {
this.pageId = pageId;
}

public long getPageTotal() {
return pageTotal;
}

public void setPageTotal(long pageTotal) {
this.pageTotal = pageTotal;
}
}

class CustomerDao {
public boolean insert(Customer customer) {
String sql = "insert into customer(username,password)values('"
+ customer.getUsername() + "','" + customer.getPassword()
+ "');";
return DBUtil.execute(sql);
}

public boolean update(Customer customer) {
String sql = "update customer set username='" + customer.getUsername()
+ "',password='" + customer.getPassword() + "' where id=7";
return DBUtil.execute(sql);
}

public boolean delete(int id) {
String sql = "delete from customer where id=" + id;
return DBUtil.execute(sql);
}
public List<Customer> query(Customer c){
List<Map<String,Object>> values=DBUtil.executeQuery(Customer.makeSql(c));
return Customer.fillList(values);
}
public ExistsStatus<Customer> findById(int id) {
Customer c=new Customer();
c.setId(id);
List<Customer> list =query(c);
ExistsStatus<Customer> result = new ExistsStatus<Customer>();
if (list.size() > 0) {
Customer customer =list.get(0);
result.setT(customer);
result.setExists(true);
}
return result;
}
public PageBean<Customer> getPage(Customer c,int pageId,int pageSize){
String sql=Customer.makeSql(c);
PageBean<Map<String,Object>> page=DBUtil.executePage(sql,pageId,pageSize);
PageBean<Customer> result=new PageBean<Customer>();
result.setPageId(page.getPageId());
result.setPageTotal(page.getPageTotal());
result.setPageContent(Customer.fillList(page.getPageContent()));
return result;
}
}

public class Test {
public static void main(String[] args) {
String sql = "select * from customer ";
int pageSize = 3;
int pageId = 2;
PageBean<Map<String,Object>> pageBean=DBUtil.executePage(sql, pageSize,pageId);
List<Map<String, Object>> list = pageBean.getPageContent();
System.out.println(sql);
System.out.println(list.size());
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
Integer id = (Integer) map.get("id");
String username = (String) map.get("username");
String password = (String) map.get("password");
System.out.println(id + " " + username + " " + password);
}
}

}