jdbc增删改查封装

package until;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



public class DbUtil_IDUS {

	//分页属性
	private int currpage = 1;// 当前页
	private int pagesize = 5;// 每页条数
	private int recordcount = 0;// 总条数
	private int pagecount = 0;// 总页数
	
	private Connection conn = null;//链接数据库对象
	private String dbname = "users";//数据库名
	private int port = 3306;//端口号
	private String username = "root";//数据库名
	private String pwd = "root";//数据库名
	
	
	/**
	 * 链接数据库
	 * @param database
	 * @param port
	 * @param name
	 * @param pwd
	 * @return
	 */
	public  Connection connection(String database, int port, String name, String pwd) {
		Connection conn=null;
		try {
			//1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//建立连接
			String url="jdbc:mysql://localhost:"+port+"/"+database+"?useUnicode=true&characterEncoding=utf8";
			 conn=DriverManager.getConnection(url, name, pwd);
			//4判断是否连接成功
			if(!conn.isClosed()){
				System.out.println("数据库连接成功");
			}else {
				System.out.println("数据库连接失败");
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 获取表中主键
	 * @param tablename
	 * @return
	 */
	public String getPk(String tablename) {
		String pk = null;
		DatabaseMetaData dbmd;
		try {
			dbmd = this.conn.getMetaData();
			ResultSet rs = dbmd.getPrimaryKeys(this.dbname, null, tablename);
			if (rs.next()) {
				pk = rs.getString(4);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pk;
	}
	
	//添加数据
	/**
	 * 以数组形式添加  1、sql语句   2.数组
	 * @param sql
	 * @param values
	 * @return
	 */
	public int add(String sql, Object[] values) {
		int num = 0;
		PreparedStatement pst;
		try {
			pst = this.conn.prepareStatement(sql);
			int i = 0;
			for (Object o : values) {
				pst.setObject(++i, o);
			}
			num = pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	/**
	 * 以map添加数据 1.表名  2.map
	 * @param tablename
	 * @param m
	 * @return
	 */
	public int insert(String tablename, Map
 
   m) {
		int num = 0;
		StringBuilder n = new StringBuilder();
		StringBuilder v = new StringBuilder();
		for (String k : m.keySet()) {
			v.append("?,");
			n.append(k + ",");
		}
		String sql = String.format("insert into %s(%s) values(%s)", tablename, n.toString().subSequence(0, n.length() - 1), v.toString().subSequence(0, v.length() - 1));
		PreparedStatement pst;
		try {
			pst = this.conn.prepareStatement(sql);
			int i = 0;
			for (Object o : m.values()) {
				pst.setObject(++i, o);
			}
			num = pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	//修改数据
	/**
	 * 以主键修改需要的字段
	 * @param tablename
	 * @param m
	 * @return
	 */
	public int update(String tablename, Map
  
    m) { int num = 0; String pk = this.getPk(tablename); if (m.containsKey(pk)) { num = update(tablename, m, pk + "=" + m.get(pk)); } else { num = update(tablename, m, "1=1"); } return num; } /** * 按条件修改需要的字段 * @param tablename * @param m * @param where * @return */ public int update(String tablename, Map
   
     m, String where) { int num = 0; StringBuilder s = new StringBuilder(); for (String k : m.keySet()) { s.append(k + "=?,"); } String sql = String.format("update %s set %s where %s", tablename, s.toString().subSequence(0, s.length() - 1), where); PreparedStatement pst; try { pst = this.conn.prepareStatement(sql); int i = 0; for (Object o : m.values()) { pst.setObject(++i, o); } num = pst.executeUpdate(); pst.close(); } catch (SQLException e) { e.printStackTrace(); } return num; } //删除数据 /** * 删除表 * @param tablename * @return */ public int delete(String tablename) { int num = delete(tablename, "1=1"); return num; } /** * 以id删除数据 * @param tablename * @param id * @return */ public int deleteById(String tablename, Object id) { int num = delete(tablename, this.getPk(tablename) + "=" + id); return num; } /** * 以 where 条件删除数据 * @param tablename * @param where * @return */ public int delete(String tablename, String where) { int num = 0; String sql = String.format("delete from %s where %s", tablename, where); try { PreparedStatement pst = this.conn.prepareStatement(sql); num = pst.executeUpdate(); pst.close(); } catch (SQLException e) { e.printStackTrace(); } return num; } // 查询数据 /** * 以id查询某条数据 * @param tablename * @param id * @return */ public Map
    
      queryById(String tablename, Object id) { Map
     
       m = new HashMap
      
       (); String sql = String.format("select * from %s where %s", tablename, this.getPk(tablename) + "=" + id); try { PreparedStatement pst = this.conn.prepareStatement(sql); ResultSet rs = pst.executeQuery(); if (rs.next()) { ResultSetMetaData rsmd = rs.getMetaData(); int cc = rsmd.getColumnCount(); for (int i = 1; i <= cc; i++) { String name = rsmd.getColumnLabel(i); m.put(name, rs.getObject(name)); } } } catch (SQLException e) { e.printStackTrace(); } return m; } //分页逻辑 /** * 分页：1.当前页 2.表名 * @param currpage * @param tablename * @return */ public List
       
        > page(int currpage, String tablename) { return page(currpage,tablename,"*","where 1=1",""); } /** * 分页：1.当前页 2.表名 3.排序 * @param currpage * @param tablename * @param order * @return */ public List
        
         > page(int currpage, String tablename,String order) { return page(currpage,tablename,"*","where 1=1",order); } /** * 分页：1.当前页 2.表名 3.条件 4.排序 * @param currpage * @param tablename * @param where * @param order * @return */ public List
         
          > page(int currpage, String tablename,String where,String order) { return page(currpage,tablename,"*",where,order); } /** * 分页：1.当前页 2.表名 3.查询字段 4.条件 5.排序 * @param currpage * @param tablename * @param fields * @param where * @param order * @return */ public List
          
           > page(int currpage, String tablename, String fields, String where, String order) { //当前页 this.currpage = currpage; //实例化map集合 List
           
            > list = new ArrayList
            
             >(); //查询sql String sql = String.format("select %s from %s %s %s limit ?,?", fields, tablename, where, order); //查询总条数 String qqq = String.format("select count(*) c from %s %s", tablename, where); try { // 分页信息 PreparedStatement qpst = this.conn.prepareStatement(qqq); ResultSet qrs = qpst.executeQuery(); if (qrs.next()) { //总条数 this.recordcount = qrs.getInt("c"); //总页数 this.pagecount = this.recordcount % this.pagesize == 0 ? this.recordcount / this.pagesize : this.recordcount / this.pagesize + 1; } //当前页 if(this.currpage < 1) this.currpage = 1; if(this.currpage > this.pagecount) this.currpage = this.pagecount; // 分页结果信息 PreparedStatement pst = this.conn.prepareStatement(sql); pst.setInt(1, this.currpage * this.pagesize - this.pagesize); pst.setInt(2, this.pagesize); ResultSet rs = pst.executeQuery(); //获取数据表信息 ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { Map
             
               m = new HashMap
              
               (); //获取数据表列数 int cc = rsmd.getColumnCount(); for (int i = 1; i <= cc; i++) { //获取列的别名 String name = rsmd.getColumnLabel(i); //String name = rsmd.getColumnName(i); //获取列名 //System.out.println(name+"***"); m.put(name, rs.getObject(name)); } list.add(m); } } catch (SQLException e) { e.printStackTrace(); } return list; } /** * bootstrap 分页样式 * @return */ public String pagebootstrap() { StringBuilder s = new StringBuilder(); s.append("
               

                "); int start = 1; int end = 10; if (this.currpage >= 7) { start = this.currpage - 5; end = this.currpage + 4; } if (this.currpage != 1) { s.append(String.format("
                
上一页
", this.currpage - 1)); } for (int i = start; i <= end; i++) { if (i > this.pagecount) break; if (this.currpage == i) { s.append(String.format("
                
%d
", i)); continue; } s.append(String.format("
                
%d
", i, i)); } if (this.currpage < this.pagecount) { s.append(String.format("
                
下一页
", this.currpage + 1)); } s.append("
               
"); return s.toString(); } /** * 分页样式 * @return */ public String pageinfo(){ StringBuilder s = new StringBuilder(); s.append("
               
                "); int start = 1; int end = 10; if(this.currpage>=7){ start = this.currpage-5; end = this.currpage+4; } if(this.currpage!=1){ s.append(String.format("
                上一页",this.currpage-1)); } for(int i=start;i<=end;i++){ if(i>this.pagecount) break; if(this.currpage == i){ s.append(String.format("
                %d",i)); continue; } s.append(String.format("
                %d",i,i)); } if(this.currpage
                
               "); return s.toString(); } //get,set方法 public int getCurrpage() { return currpage; } public void setCurrpage(int currpage) { this.currpage = currpage; } public int getPagesize() { return pagesize; } public void setPagesize(int pagesize) { this.pagesize = pagesize; } public int getRecordcount() { return recordcount; } public void setRecordcount(int recordcount) { this.recordcount = recordcount; } public int getPagecount() { return pagecount; } public void setPagecount(int pagecount) { this.pagecount = pagecount; } //默认数据库，端口号，用户名，密码 public DbUtil_IDUS() { this.conn = connection(this.dbname, this.port, this.username, this.pwd); } //自定义数据库，端口号，用户名，密码 public DbUtil_IDUS(String dbname, int port, String username, String pwd) { this.dbname = dbname; this.conn = connection(dbname, port, username, pwd); } }