java jdbc宠物商店

package dao; import com.jntoo.db.utils.StringUtil; import java.sql.*; import java.util.*; import javax.servlet.http.HttpServletRequest; import util.Info; /** * 数据库连接类 */ public class CommDAO { // 数据库名称 public static final String database = "jspm11758cwsd"; // 数据库账号 public static final String username = "root"; // 数据库密码 public static final String pwd = "root"; // 是否为 mysql8.0及以上、如果是则把 false 改成 true public static final boolean isMysql8 = false; // 是否为mysql8 public static Connection conn = null; /** * 创建类时即连接数据库 */ public CommDAO() { conn = this.getConn(); } /** * 数据库链接类 * @return */ public static Connection getConn() { try { if (conn == null || conn.isClosed()) { String connstr = getConnectString(); conn = DriverManager.getConnection(connstr, username, pwd); } } catch (Exception e) { e.printStackTrace(); } return conn; } public static String getConnectString() { try { String connstr; // if (!isMysql8) { // Class.forName("com.mysql.jdbc.Driver"); // connstr = String.format("jdbc:mysql://localhost:3306/%s?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true", database); // } else { // Class.forName("com.mysql.cj.jdbc.Driver"); // connstr = // String.format( // "jdbc:mysql://localhost:3306/%s?useUnicode=true&characterEncoding=UTF-8&useSSL=FALSE&serverTimezone=UTC&useOldAliasMetadataBehavior=true", // database // ); // } Class.forName("com.mysql.jdbc.Driver"); connstr = String.format("jdbc:mysql://127.0.0.1:3306/%s?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true", database); return connstr; } catch (Exception e) { e.printStackTrace(); } return ""; } /** * 根据表ID 获取数据 * @param id 数值 * @param table 表名称 * @return */ public HashMap getmap(String id, String table) { List<HashMap> list = new ArrayList(); try { Statement st = conn.createStatement(); //System.out.println("select * from "+table+" where id="+id); ResultSet rs = st.executeQuery("select * from " + table + " where id=" + id); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { HashMap map = new HashMap(); int i = rsmd.getColumnCount(); for (int j = 1; j <= i; j++) { if (!rsmd.getColumnName(j).equals("ID")) { String str = rs.getString(j) == null ? "" : rs.getString(j); if (str.equals("null")) str = ""; map.put(rsmd.getColumnName(j), str); } else map.put("id", rs.getString(j)); } list.add(map); } rs.close(); st.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list.get(0); } /** * 根据sql 语句获取一行数据 * @param sql * @return */ public HashMap find(String sql) { HashMap map = new HashMap(); //List<HashMap> list = new ArrayList(); try { Statement st = conn.createStatement(); System.out.println(sql); ResultSet rs = st.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { //HashMap map = new HashMap(); int i = rsmd.getColumnCount(); for (int j = 1; j <= i; j++) { if (!rsmd.getColumnName(j).equals("ID")) { String str = rs.getString(j) == null ? "" : rs.getString(j); if (str.equals("null")) str = ""; map.put(rsmd.getColumnName(j), str); } else map.put("id", rs.getString(j)); } //list.add(map); break; } rs.close(); st.close(); } catch (SQLException e) { // TODO Auto-generated catch block //e.printStackTrace(); int code = e.getErrorCode(); String message = e.getMessage(); System.err.println("SQL execute Error"); System.err.println("code:" + code); System.err.println("Message:" + message); } return map; } /** * 根据某字段的值获取一行数据 * @param nzd 字段名称 * @param zdz 条件值 * @param table 表 * @return */ public HashMap getmaps(String nzd, String zdz, String table) { List<HashMap> list = new ArrayList(); try { Statement st = conn.createStatement(); //System.out.println("select * from "+table+" where "+nzd+"='"+zdz+"'"); ResultSet rs = st.executeQuery("select * from " + table + " where " + nzd + "='" + zdz + "'"); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { HashMap map = new HashMap(); int i = rsmd.getColumnCount(); for (int j = 1; j <= i; j++) { if (!rsmd.getColumnName(j).equals("ID")) { String str = rs.getString(j) == null ? "" : rs.getString(j); if (str.equals("null")) str = ""; map.put(rsmd.getColumnName(j), str); } else map.put("id", rs.getString(j)); } list.add(map); } rs.close(); st.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return list.get(0); } /** * 获取前台提交的数据将数据写成Map<String , String> 形式，方便写入数据库 * @param request * @return 返回值类型为Map<String, String> */ public static HashMap getParameterStringMap(HttpServletRequest request) { Map<String, String[]> properties = request.getParameterMap(); //把请求参数封装到Map<String, String[]>中 HashMap returnMap = new HashMap<String, String>(); String name = ""; String value = ""; for (Map.Entry<String, String[]> entry : properties.entrySet()) { name = entry.getKey(); String[] values = entry.getValue(); if (null == values) { value = ""; } else { value = StringUtil.join(",", values); //用于请求参数中请求参数名唯一 } returnMap.put(name, value); } return returnMap; } /** * 插入数据库 * @param request * @param tablename * @param extmap * @return */ public String insert(HttpServletRequest request, String tablename, HashMap extmap) { extmap.put("addtime", Info.getDateStr()); // 设置添加时间为当前时间 Query query = new Query(tablename); // 新建查询模块 HashMap post = getParameterStringMap(request); // 获取前台提交的数据将数据写成Map对象 post.putAll(extmap); // 扩展的数据以覆盖方式写到提交的数据中 return query.add(post); // 将数据生成sql insert语句,并执行,可以查看输出控制台中执行的SQL语句 } /** * 删除数据 * @param request * @param tablename 表名称