com.bh.nora.dao
ToyDao:
package com.bh.nora.dao;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.bh.nora.util.JDBCUtils;
/**
@author Nora
*/
public class ToyDao {
/**
*
* @param pageNumber 页数
* @param pageSize 每页显示个数
* @param username 用户名称
* @param toyName 玩具名称
* @param minPrice 最小价格
* @param maxPrice 最大价格
* @return
* @throws Exception
*/
public Map<String, Object> list(Integer pageNumber, Integer pageSize, String username, String toyName, Integer minPrice, Integer maxPrice) throws Exception{
String sql = " select "
+ " t.id, t.toyname, tt.typename, t.price, t.intro, t.comment "
+ " from "
+ " user as u "
+ " left join shoppingcart as sc on u.id = sc.userid "
+ " left join toy as t on sc.toyid = t.id "
+ " left join toytype as tt on t.type = tt.id "
+ " where "
+ " u.username = ? ";
String countSql = "select count(1) from "
+ " user as u "
+ " left join shoppingcart as sc on u.id = sc.userid "
+ " left join toy as t on sc.toyid = t.id "
+ " left join toytype as tt on t.type = tt.id "
+ " where "
+ " u.username = ? ";
List<Object> params = new ArrayList<Object>();
if (username == null ) {
System.out.print("username is required");
return null;
}
params.add(username.trim());
if (toyName != null && toyName.trim().length() > 0) {
sql += " and t.toyname like ? ";
countSql += " and t.toyname like ? ";
params.add("%" + toyName + "%");
}
if (minPrice != null) {
sql += " and t.price >= ? ";
countSql += " and t.price >= ? ";
params.add(minPrice);
}
if (maxPrice != null) {
sql += " and t.price <= ? ";
countSql += " and t.price <= ? ";
params.add(maxPrice);
}
pageNumber = pageNumber == null || pageNumber <= 0 ? 1 : pageNumber;
pageSize = pageSize == null || pageSize <= 0 ? 10 : pageSize;
sql += " limit " + (pageNumber - 1) * pageSize + ", " + pageSize;
List<Map<String, Object>> data = JDBCUtils.selectCondiationByMap(sql, params.toArray());
Long count = JDBCUtils.get(countSql, params.toArray());
Map<String, Object> result = new HashMap<String, Object>();
result.put("data", data);
result.put("count", count);
return result;
}
}
com.bh.nora.pojo
ShoppingCart:
package com.bh.nora.pojo;
/**
@author Nora
*/
public class ShoppingCart {
private Integer id;
private Integer userId;
private Integer toyId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Integer getToyId() {
return toyId;
}
public void setToyId(Integer toyId) {
this.toyId = toyId;
}
@Override
public String toString() {
return "ShoppingCart [id=" + id + ", userId=" + userId + ", toyId=" + toyId + "]";
}
}
Toy:
package com.bh.nora.pojo;
/**
@author Nora
*/
public class Toy {
private Integer id;
private String toyName;
private String intro;
private Integer price;
private Integer type;
private String comment;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getToyName() {
return toyName;
}
public void setToyName(String toyName) {
this.toyName = toyName;
}
public String getIntro() {
return intro;
}
public void setIntro(String intro) {
this.intro = intro;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
public Integer getType() {
return type;
}
public void setType(Integer type) {
this.type = type;
}
public String getComment() {
return comment;
}
public void setComment(String comment) {
this.comment = comment;
}
@Override
public String toString() {
return "Toy [id=" + id + ", toyName=" + toyName + ", intro=" + intro + ", price=" + price + ", type=" + type
+ ", comment=" + comment + "]";
}
}
ToyType:
package com.bh.nora.pojo;
/**
@author Nora
*/
public class ToyType {
private Integer id;
private String toyType;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getToyType() {
return toyType;
}
public void setToyType(String toyType) {
this.toyType = toyType;
}
@Override
public String toString() {
return "ToyType [id=" + id + ", toyType=" + toyType + "]";
}
}
User:
package com.bh.nora.pojo;
/**
@author Nora
*/
public class User {
private Integer id;
private String username;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + "]";
}
}
com.bh.nora.util
JDBCUtils:
package com.bh.nora.util;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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;
public class JDBCUtils {
private static final String noraUrl = "jdbc:mysql://localhost:3306/db_test_d?useSSL=false&serverTimezone=GMT%2B8";
private static final String noraName = "root";
private static final String noraPass = "123456";
/**
* 获取数据库连接
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(noraUrl, noraName, noraPass);
}
@SuppressWarnings("unchecked")
public static <T> T get(String sql, Object [] args) throws SQLException {
Connection noraConn = JDBCUtils.getConnection();
PreparedStatement noraPs = noraConn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
noraPs.setObject(i + 1, args[i]);
}
ResultSet noraRs = noraPs.executeQuery();
Object o = null;
if (noraRs.next()) {
o = noraRs.getObject(1);
destory(noraConn, noraPs, noraRs);
return (T)o;
}
return null;
}
public static <E> List<E> selectCondiation(String sql, Class<E> clazz, Object [] args) throws Exception {
Connection noraConn = JDBCUtils.getConnection();
PreparedStatement noraPs = noraConn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
noraPs.setObject(i + 1, args[i]);
}
ResultSet noraRs = noraPs.executeQuery();
ResultSetMetaData noraRsmd = noraRs.getMetaData();
int columnCount = noraRsmd.getColumnCount();
List<E> results = new ArrayList<E>();
while(noraRs.next()) {
E o = clazz.getConstructor().newInstance();
for (int i = 0 ; i < columnCount; i++) {
String columnLabel = noraRsmd.getColumnLabel(i + 1);
Object columnValue = noraRs.getObject(columnLabel);
Field field = clazz.getDeclaredField(columnLabel);
Method method = clazz.getMethod(setMethod(columnLabel), field.getType());
method.invoke(o, columnValue);
}
results.add(o);
}
destory(noraConn, noraPs, noraRs);
return results;
}
public static String setMethod(String methodName) {
return "set" + methodName.substring(0, 1).toUpperCase() + methodName.substring(1);
}
public static List<Map<String, Object>> selectCondiationByMap(String sql, Object [] args) throws Exception {
Connection noraConn = JDBCUtils.getConnection();
PreparedStatement noraPs = noraConn.prepareStatement(sql);
for(int i = 0; i < args.length; i++) {
noraPs.setObject(i + 1, args[i]);
}
ResultSet noraRs = noraPs.executeQuery();
ResultSetMetaData noraNsmd = noraRs.getMetaData();
int columnCount = noraNsmd.getColumnCount();
List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
while(noraRs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0 ; i < columnCount; i++) {
String columnLabel = noraRsmd.getColumnLabel(i + 1);
Object columnValue = noraRs.getObject(columnLabel);
map.put(columnLabel, columnValue);
}
results.add(map);
}
destory(noraConn, noraPs, noraRs);
return results;
}
public static void destory(Connection noraConn, Statement noraSt, ResultSet noraRs) {
if(noraRs != null) {
try {
noraRs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (noraSt != null) {
try {
noraSt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (noraConn != null) {
try {
noraConn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
com.bh.nora.app
MainApp:
package com.bh.nora.app;
import java.util.Map;
import java.util.Scanner;
import com.bh.nora.dao.ToyDao;
public class MainApp {
public static void main(String[] args) throws Exception {
ToyDao noraToyDao = new ToyDao();
Scanner sc = new Scanner(System.in);
System.out.println("请输入页数, 例如: 1, 2, ...");
String pageNumberStr = sc.nextLine();
Integer pageNumber = null;
try {
pageNumber = Integer.valueOf(pageNumberStr);
} catch (NumberFormatException e) {
pageNumber = null;
}
System.out.println("请输入每页显示多少条数据,例如: 10, 20, ...");
String pageSizeStr = sc.nextLine();
Integer pageSize = null;
try {
pageSize = Integer.valueOf(pageSizeStr);
} catch (NumberFormatException e) {
pageSize = null;
}
String username = null;
while(true) {
username = sc.nextLine();
if (username != null && username.trim().length() != 0) {
break;
} else {
System.out.println("用户名称为必填项!");
continue;
}
}
System.out.print("请输入玩具名称");
String toyName = sc.nextLine();
System.out.println("请输入金额下限");
String minPirceStr = sc.nextLine();
Integer minPirce = null;
try {
minPirce = Integer.valueOf(minPirceStr);
} catch (NumberFormatException e) {
minPirce = null;
}
System.out.println("请输入金额上限");
String maxPirceStr = sc.nextLine();
Integer maxPirce = null;
try {
maxPirce = Integer.valueOf(maxPirceStr);
} catch (NumberFormatException e) {
maxPirce = null;
}
System.out.println("pageNumber: " + pageNumber + ", pageSize: " + pageSize + ", username: " + username + ", toyName: " + toyName + ", minPirce: " + minPirce + ", maxPirce: " + maxPirce);
Map<String, Object> result = noraToyDao.list(pageNumber, pageSize, username, toyName, minPirce, maxPirce);
System.out.print(result);
// 关闭输入流
sc.close();
}
}