java ajax级联_【Java】Ajax实现级联城市

packagepers.dbutils;importlombok.Data;importjava.io.IOException;importjava.io.InputStream;import java.sql.*;import java.util.*;importjava.util.stream.Collectors;/*** TODO jdbc常用操作工具类

*

*@authornetyts@163.com

* @date 2020/11/6 15:24*/@Datapublic classDbUtils {private String driver = "com.mysql.cj.jdbc.Driver";private String url = "jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8";private String user = "root";private String password = "root";privateConnection conn;private int currentPage = 1; //当前页

private int pageCount = 0; //总页数

private int pageSize = 10; //每一页记录的数据量

private int recordCount = 0; //总数据量

/*** 无参构造连接数据库*/

publicDbUtils() {try{

Class.forName(driver);this.conn =DriverManager.getConnection(url, user, password);

}catch(SQLException throwables) {

throwables.printStackTrace();

}catch(ClassNotFoundException e) {

e.printStackTrace();

}

}/*** 使用数据库名

*@paramdbName 数据库名*/

publicDbUtils(String dbName){try{

Class.forName(driver);

url= "jdbc:mysql://localhost:3306/"+dbName+"?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8";this.conn =DriverManager.getConnection(url, user, password);

}catch(Exception e) {

e.printStackTrace();

}

}/*** 有参连接数据库

*@paramdriver 加载驱动

*@paramhost IP地址

*@paramport 端口号

*@paramdbName 数据库名称

*@paramuser 用户名

*@parampassword 密码*/

publicDbUtils(String driver, String host, String port, String dbName, String user, String password) {this.driver =driver;this.url = "jdbc:mysql://" + host + ":" + port + "/" + dbName + "?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8";this.user =user;this.password =password;try{

Class.forName(driver);this.conn = DriverManager.getConnection(this.url, this.user, this.password);

}catch(SQLException throwables) {

throwables.printStackTrace();

}catch(ClassNotFoundException e) {

e.printStackTrace();

}

}//利用db.properties配置文件连接数据库

public DbUtils(booleanflag) {

InputStream is= DbUtils.class.getClassLoader().getResourceAsStream("db.properties");

Properties prop= newProperties();try{

prop.load(is);

driver= prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");

url= prop.getProperty("db.url", "jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8");

user= prop.getProperty("db.user", "root");

password= prop.getProperty("db.password", "");

pageSize= Integer.parseInt(prop.getProperty("db.pageSize", "10"));

Class.forName(driver);

conn=DriverManager.getConnection(url, user, password);

}catch(IOException e) {

e.printStackTrace();

}catch(SQLException throwables) {

throwables.printStackTrace();

}catch(ClassNotFoundException e) {

e.printStackTrace();

}

}/*** 数据库数据语句操作(增、删、改)

*

*@paramsql sql语句

*@paramparams 0个或多个

*@return对数据表产生影响的行数*/

public intexecute(String sql, Object... params) {int row = 0;try{

PreparedStatement ps=conn.prepareStatement(sql);int index = 1;for(Object p : params) {

ps.setObject(index++, p);

}

row=ps.executeUpdate();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnrow;

}/*** 数据插入

*

*@paramtableName 表名

*@paramvalues Map(key,values)键值对

*@return对数据表产生影响的行数*/

public int insert(String tableName, Mapvalues) {int row = 0;

Set set =values.keySet();

String fn= set.toString().replace(" ", "");

fn= fn.substring(1, fn.length() - 1);

String fv= set.stream().map(m -> "?").collect(Collectors.toList()).toString().replace(" ", "");

fv= fv.substring(1, fv.length() - 1);

String sql= String.format("insert into %s(%s) values(%s)", tableName, fn, fv);try{

PreparedStatement ps=conn.prepareStatement(sql);int index = 1;for(String k : set) {

ps.setObject(index++, values.get(k));

}

row=ps.executeUpdate();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnrow;

}/*** 数据插入

*

*@paramtableName 表名

*@paramfieldName 字段名(1个或多个)

*@paramfieldValues 数据信息(和字段名顺序保持一致)

*@return对数据表产生影响的行数*/

public intinsert(String tableName, String fieldName, Object[] fieldValues) {int row = 0;

String fv= Arrays.stream(fieldValues).map(m -> "?").collect(Collectors.toList()).toString().replace(" ", "");

fv= fv.substring(1, fv.length() - 1);

String sql= String.format("insert into %s(%s) values(%s)", tableName, fieldName, fv);try{

PreparedStatement ps=conn.prepareStatement(sql);for (int i = 0; i < fieldValues.length; i++) {

ps.setObject(i+ 1, fieldValues[i]);

}

row=ps.executeUpdate();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnrow;

}/*** 根据主键删除数据

*@paramtableName 表名

*@paramparams 主键值(1个或多个)

*@return对数据表产生影响的行数*/

public intdeleteByPK(String tableName, Object... params){int row = 0;

String symbol= "?,".repeat(params.length);

String sql= String.format("delete from %s where %s in(%s)", tableName,getPK(tableName),symbol.substring(0, symbol.length()-1));try{

PreparedStatement ps=conn.prepareStatement(sql);int index = 1;for(Object o : params){

ps.setObject(index++,o);

}

row=ps.executeUpdate();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnrow;

}/*** 根据主键修改数据

*@paramtableName 表名

*@paramfieldName 字段名(1个或多个)

*@paramvalues 修改后的值

*@paramparams 主键值(1个或多个)

*@return对数据表产生影响的行数*/

public intupdateByPK(String tableName, String fieldName, Object values, Object... params){int row = 0;

String symbol= "?,".repeat(params.length);

String sql= String.format("update %s set %s=? where %s in(%s)",tableName,fieldName,getPK(tableName),symbol.substring(0, symbol.length()-1));try{

PreparedStatement ps=conn.prepareStatement(sql);

ps.setObject(1, values);int index = 2;for(Object o : params){

ps.setObject(index++, o);

}

row=ps.executeUpdate();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnrow;

}/*** 对某一列(某几个)的值全部增加或减少相同的值

*

*@paramtableName 表名

*@paramfieldName 字段名(1个或多个)

*@paramvalues 要增加或减少的值

*@paramcondition 条件

*@return对数据表产生影响的行数*/

public intupdateInc(String tableName, String fieldName, Object values, String condition) {int row = 0;

String sql= String.format("update %s set %2$s=%s+%d %s", tableName, fieldName, values, condition);try{

PreparedStatement ps=conn.prepareStatement(sql);

row=ps.executeUpdate();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnrow;

}/*** 获取主键的字段名

*@paramtableName 表名

*@return表的主键字段名*/

publicString getPK(String tableName) {

String PKName= null;try{

DatabaseMetaData dmd=conn.getMetaData();

ResultSet rs= dmd.getPrimaryKeys(null, "%", tableName);

rs.next();

PKName= rs.getString("column_name");

rs.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}// //方法二//String sql = String.format("show index from %s", tableName);//try {//PreparedStatement ps = conn.prepareStatement(sql);//ResultSet rs = ps.executeQuery();//rs.next();//PKName = rs.getString("column_name");//rs.close();//ps.close();//} catch (SQLException throwables) {//throwables.printStackTrace();//}

returnPKName;

}/*** 查询操作

*@paramsql sql语句

*@paramparams 0个或多个

*@returnlist集合*/

public List>select(String sql, Object... params) {

List> list = new LinkedList<>();try{

PreparedStatement ps=conn.prepareStatement(sql);int index = 1;for(Object o : params) {

ps.setObject(index++, o);

}

ResultSet rs=ps.executeQuery();

ResultSetMetaData rsm=rs.getMetaData();

Mapm;while(rs.next()) {

m= new LinkedHashMap<>();for (int i = 1; i <= rsm.getColumnCount(); i++) {

m.put(rsm.getColumnLabel(i), rs.getObject(rsm.getColumnLabel(i)));

}

list.add(m);

}

rs.close();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnlist;

}/*** 查询操作

*@paramtableName 表名

*@paramfieldName 字段名(1个或多个)

*@paramcondition 条件

*@returnlist集合*/

public List>select(String tableName, String fieldName, String condition) {

String sql= String.format("select %s from %s %s", fieldName, tableName, condition);returnselect(sql);

}//private int currPage = 1;//当前页//private int pageCount = 0;//总页数//private int pageSize = 10;//每一页的数据量//private int recordCount = 0;//总数据量

/*** 获取总数据数量

*@paramtableName 表名

*@paramcondition 条件

*@return总数据量*/

public intgetRecordCount(String tableName, String condition) {

String sql= String.format("select count(*) from %s %s", tableName, condition);try{

PreparedStatement ps=conn.prepareStatement(sql);

ResultSet rs=ps.executeQuery();

rs.next();

recordCount= rs.getInt(1);

rs.close();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}returnrecordCount;

}/*** 获取总页数

*@paramtableName 表名

*@parampageSize 每一页记录的数据量

*@paramcondition 条件

*@return总页数*/

public int getPageCount(String tableName, intpageSize, String condition) {

recordCount=getRecordCount(tableName, condition);if (recordCount % pageSize == 0) {

pageCount= recordCount /pageSize;

}else{

pageCount= recordCount / pageSize + 1;

}returnpageCount;

}/*** 获取总页码(每页默认显示10条数据)

*@paramtableName 表名

*@paramcondition 条件

*@return

*/

public intgetPageCount(String tableName, String condition) {returngetPageCount(tableName, pageSize, condition);

}/*** 查看某一页的数据

*@paramtableName 表名

*@paramfieldName 字段名(1个或多个)

*@paramcurrentPage 选择要查看的页数(当前页)

*@parampageSize 每页记录的数据量

*@paramcondition 条件

*@returnlist集合*/

public List> page(String tableName, String fieldName, int currentPage, intpageSize, String condition) {

pageCount=getPageCount(tableName, pageSize, condition);

List> list = new LinkedList<>();if (currentPage <=pageCount) {int cp = pageSize * (currentPage - 1);

String sql= String.format("select %s from %s limit %d,%d %s", fieldName, tableName, cp, pageSize, condition);//System.out.println(sql);

try{

PreparedStatement ps=conn.prepareStatement(sql);

ResultSet rs=ps.executeQuery();

ResultSetMetaData rsm=rs.getMetaData();

Mapm;while(rs.next()) {

m= new LinkedHashMap<>();for (int i = 1; i <= rsm.getColumnCount(); i++) {

m.put(rsm.getColumnLabel(i), rs.getObject(rsm.getColumnLabel(i)));

}

list.add(m);

}

rs.close();

ps.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}

}else{

Map m = new HashMap<>();

m.put("error", "页码输入有误！");

list.add(m);

}returnlist;

}/*** 查看某一页的数据(每页的默认10条数据，默认无条件)

*@paramtableName 表名

*@paramfieldName 字段名(1个或多个)

*@paramcurrentPage 选择要查看的页数(当前页)

*@returnlist集合*/

public List> page(String tableName, String fieldName, intcurrentPage){return page(tableName, fieldName, currentPage, pageSize, "");

}/*** 导出表数据到txt文件,需要判断secure_file_priv(show variables like "secure_file_priv";)的状态，

* 若为null，则是对mysqld的导入、导出做限制，修改my.ini文件添加或修改secure_file_priv="";

*@paramtableName 表名

*@paramfieldName 字段名(1个或多个)

*@paramfile 文件路径*/

public voidexportData(String tableName, String fieldName, String file) {

String sql= String.format("select %s from %s into outfile '%s'", fieldName,tableName,file);try{

PreparedStatement ps=conn.prepareStatement(sql);

ResultSet rs=ps.executeQuery();

ps.close();

rs.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}

}/*** 导入表数据，数据库必须有此表的表结构

*@paramfile 文件路径

*@paramtableName 表名*/

public voidimportData(String file, String tableName) {

String sql= String.format("load data infile '%s' into table %s",file,tableName);try{

PreparedStatement ps=conn.prepareStatement(sql);

ResultSet rs=ps.executeQuery();

ps.close();

rs.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}

}public voidclose() {try{

conn.close();

}catch(SQLException throwables) {

throwables.printStackTrace();

}

}

}