JDBC连接MySQL 方法（封装方法，把增删改查操作封装在函数中）

来源：互联网发布：mac如何往u盘拷贝编辑：程序博客网时间：2024/05/11 15:17
package com.wanyifei.bean;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.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;public class ConnectionDB { /**  * 数据库驱动类名称  */ private static final String DRIVER = "com.mysql.jdbc.Driver"; /**  * 连接字符串  */ private static final String URLSTR = "jdbc:mysql://localhost:3306/BookStore"; /**  * 用户名  */ private static final String USERNAME = "root"; /**  * 密码  */ private static final String USERPASSWORD = "mysql"; /**  * 创建数据库连接类  */ private Connection connnection = null; private PreparedStatement preparedStatement = null; /**  * 创建结果集对象  */ private ResultSet resultSet = null;  static {  try {   // 加载数据库驱动程序   Class.forName(DRIVER);  } catch (ClassNotFoundException e) {   System.out.println("加载驱动错误");   System.out.println(e.getMessage());  } } /**  * 建立数据库连接  *  * @return  */ public Connection getConnection() {  try {   // 获取连接   connnection = DriverManager.getConnection(URLSTR, USERNAME,     USERPASSWORD);  } catch (SQLException e) {   System.out.println(e.getMessage());  }  return connnection; }  public int executeUpdate(String sql, Object[] params) {  int affectedLine = 0;  try {   connnection = this.getConnection();   preparedStatement = connnection.prepareStatement(sql);   for (int i = 0; i < params.length; i++) {    preparedStatement.setObject(i + 1, params[i]);   }   affectedLine = preparedStatement.executeUpdate();  } catch (SQLException e) {   System.out.println(e.getMessage());  } finally {   closeAll();  }  return affectedLine; }  /**  * 查询语句     * @param sql SQL语句  * @param params SQL语句  * @return List结果集  */ private ResultSet executeQueryRS(String sql, Object[] params) {  try {   connnection = this.getConnection();   preparedStatement = connnection.prepareStatement(sql);   for (int i = 0; i < params.length; i++) {    preparedStatement.setObject(i + 1, params[i]);   }   resultSet = preparedStatement.executeQuery();  } catch (SQLException e) {   System.out.println(e.getMessage()); }    return resultSet; }  /**  * 获取结果集，并将结果放在List中  * @param sql SQL语句  * @return List结果集  */ public List<Object> excuteQuery(String sql, Object[] params) {  ResultSet rs = executeQueryRS(sql,params);  ResultSetMetaData rsmd = null;  int columnCount = 0;  try {   rsmd = rs.getMetaData();   columnCount = rsmd.getColumnCount();  } catch (SQLException e1) {   System.out.println(e1.getMessage());  }    List<Object> list = new ArrayList<Object>();    try {   while(rs.next()) {    Map<String, Object> map = new HashMap<String, Object>();    for(int i = 1; i <= columnCount; i++) {     map.put(rsmd.getColumnLabel(i), rs.getObject(i));        }    list.add(map);   }  } catch (SQLException e) {   System.out.println(e.getMessage());  } finally {   closeAll();  }    return list; } /** * 撤销链接 */ private void closeAll() {  if (resultSet != null) {   try {    resultSet.close();   } catch (SQLException e) {    System.out.println(e.getMessage());   }  }  if (preparedStatement != null) {   try {    preparedStatement.close();   } catch (SQLException e) {    System.out.println(e.getMessage());   }  }    if (connnection != null) {   try {    connnection.close();   } catch (SQLException e) {    System.out.println(e.getMessage());   }  } }    public static void main(String[] args) {       String[] params={"wanyifei"};     ConnectionDB connDB = new ConnectionDB();       List<Object> list = connDB.excuteQuery("SELECT * FROM User_Information where username= ?", params);       for(int i=0;i<list.size();i++)     {     System.out.println(list.get(i));     }     connDB.closeAll();   }  }
0 0