数据库连接工具类

待我称王封你为后i 2022-05-18 00:16 222阅读 0赞

DBConnection.java

package com.mysql;
    
    import java.sql.DriverManager;
    
    import com.mysql.jdbc.Connection;
    
    public final class DBConnection {
    	String driver = "com.mysql.jdbc.Driver";
        String url= "jdbc:mysql://localhost:3306/mydatabase01?useUnicode=true&characterEncoding=UTF-8";
        String user = "root";
        String password = "root";
        
        public Connection conn;
    
        public DBConnection() {
    
            try {
                Class.forName(driver);// 加载驱动程序
                conn = (Connection) DriverManager.getConnection(url, user, password);// 连续数据库
                
                
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        public void close() {
            try {
                this.conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

MysqlUtil.java

package com.mysql;
    
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import com.model.Person;
    import com.mysql.jdbc.Connection;
    import com.mysql.jdbc.PreparedStatement;
    
    public class MysqlUtil {
    	
    	public static void main(String[] args) {
    		// TODO Auto-generated method stub
    //		Person person = new Person("2011","张彤","男",22,190,80);
    //		String sqlInsert = "INSERT  INTO Person (Pno,Pname,Psex,Page,Pheight,Pweight) VALUES('";		
    //		sqlInsert += person.getPno()+"','";
    //		sqlInsert += person.getName()+"','";
    //		sqlInsert += person.getSex()+"',";
    //		sqlInsert += person.getAge()+",";
    //		sqlInsert += person.getHeight()+",";
    //		sqlInsert += person.getWeight()+")";
    //		
    //		System.out.println(add(sqlInsert));
    		String sqlquery = "select * from Person";
    		String[] params = {"Pno","Pname","Psex","Page","Pheight","Pweight"};
    		System.out.println("编号" +"\t"+ "姓名" +"\t"+ "性别"+"\t"+ "年龄"+"\t"+ "身高"+"\t"+ "体重");
    		List<Map<String,String>> listmap = new ArrayList<>();
    		listmap = show(sqlquery, params);
    		for(int i =0 ; i<listmap.size();i++) {
    			
    			for(int j=0 ; j<listmap.get(i).size();j++) {
    				System.out.print(listmap.get(i).get(params[j])+"\t");
    			}
    			System.out.println();
    		}
    		
    		
    	}
    	
    	
    	public static int add(String sql) {
            int message = 0;
         
            DBConnection db = new DBConnection();
            try {        
                PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
                preStmt.executeUpdate();
                //Statement statement = (Statement) db.conn.createStatement();
                //statement.executeUpdate(sql);
                
                preStmt.close();
                db.close();//关闭连接 
                message = 1;
            } catch (Exception e) {
            	
                e.printStackTrace();
            }
            return message;//返回影响的行数，1为执行成功
        }
        
        //查找操作
        public static List< Map<String,String> > show(String sql, String[] params){
            // String sql ="select * from employee";
       	
        	 List< Map<String,String> > listmap = new ArrayList<>();
       	
             DBConnection db = new DBConnection();
             ResultSet rs = null;
            
             try {
                Statement stmt = (Statement) db.conn.createStatement();
                rs = (ResultSet) stmt.executeQuery(sql);
                while(rs.next()){
                	
                	Map<String,String> map = new HashMap<>();
                	for(int i = 0; i < params.length; i++) {
                		
                		map.put(params[i], rs.getString(params[i]));
                	}
                	listmap.add(map);
                }
                rs.close();
                db.close();//关闭连接 
            } catch (SQLException e) {
                e.printStackTrace();
            }
    		return listmap; 
             
         
        }
        
        public static int del(String sql) {
            int i=0;
          //  String sql="delete from employee where name=?";
            DBConnection db = new DBConnection();
            try {    
                PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
             
                preStmt.executeUpdate();
                
                preStmt.close();
                db.close();//关闭连接 
                
                i = 1;
            } catch (SQLException e){
                e.printStackTrace();
            }
            return i;//返回影响的行数，1为执行成功
        }
        
        public static int update( String sql ) {
            int i =0;
           // String sql="update employee set email=?,pwd=? where name=?";
            DBConnection db = new DBConnection();
            
            try {
                PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
    //            preStmt.setString(1, uemail);
    //            preStmt.setString(2, upwd);
    //            preStmt.setString(3, uname);
                preStmt.executeUpdate();
                
                preStmt.close();
                db.close();//关闭连接 
                i = 1;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return i;//返回影响的行数，1为执行成功
        }
    
    }