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);
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 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 fillList(List> values) {
List result = new ArrayList();
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 {
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 {
private List pageContent;
private long pageId;
private long pageTotal;
public List getPageContent() {
return pageContent;
}
public void setPageContent(List 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 query(Customer c){
List> values=DBUtil.executeQuery(Customer.makeSql(c));
return Customer.fillList(values);
}
public ExistsStatus findById(int id) {
Customer c=new Customer();
c.setId(id);
List list =query(c);
ExistsStatus result = new ExistsStatus();
if (list.size() > 0) {
Customer customer =list.get(0);
result.setT(customer);
result.setExists(true);
}
return result;
}
public PageBean getPage(Customer c,int pageId,int pageSize){
String sql=Customer.makeSql(c);
PageBean> page=DBUtil.executePage(sql,pageId,pageSize);
PageBean result=new PageBean();
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> pageBean=DBUtil.executePage(sql, pageSize,pageId);
List> list = pageBean.getPageContent();
System.out.println(sql);
System.out.println(list.size());
for (int i = 0; i < list.size(); i++) {
Map 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);
}
}
}
