使用JDBC处理mysql

来源：互联网发布：toptop软件下载编辑：程序博客网时间：2024/06/07 08:26
MyEclipse添加MySQL驱动包
右键点击你的工程-->properties-->Java Build Path-->Add External JARs-->然后找到你电脑里的mysql驱动：mysql-connector-java-5.1.6-bin.jar
项目结构及其配置文件
package yjmyzz.dubbo.demo.DAO;import java.io.InputStream;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;import java.util.Properties;import org.springframework.stereotype.Service;import sun.org.mozilla.javascript.internal.ast.TryStatement;@Service("jdbcUtils")public class JdbcUtils {       private static String driver = null;private static String username = null;private static String password = null;private static String url = null;private static Connection connection;private static PreparedStatement pstmt;private static ResultSet resultSet;        //加载配置文件,并注册驱动public static void initParam(String paramFile) throws Exception{ //读取db.properties文件中的数据库连接信息InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties props = new Properties();props.load(in);driver = props.getProperty("jdbc.driver");username = props.getProperty("jdbc.username");password = props.getProperty("jdbc.password");url = props.getProperty("jdbc.url");Class.forName(driver);}//创建连接public static Connection getConnection(){        try {              connection = DriverManager.getConnection(url, username, password);          } catch (SQLException e) {              e.printStackTrace();          }          return connection;      }    /** * 增删改 * @param sql * @param params * @return * @throws SQLException */public static boolean addByPreparedStatement(String sql, List<Object> params)throws SQLException{          boolean flag = false;        int result = -1;          pstmt = connection.prepareStatement(sql);          int index = 1;          if(params != null && !params.isEmpty()){              for(int i=0; i<params.size(); i++){                  pstmt.setObject(index++, params.get(i));            }        }          result = pstmt.executeUpdate();          flag = result > 0 ? true : false;          return flag;      }/** * 查询单条记录 * @param sql * @param params sql语句中的参数 * @return 返回Map集合,key是属性名,value是值 * @throws SQLException */public static Map<String, Object> findSimpleResult(String sql, List<Object> params) throws SQLException{          Map<String, Object> map = new HashMap<String, Object>();          int index  = 1;          pstmt = connection.prepareStatement(sql);          if(params != null && !params.isEmpty()){              for(int i=0; i<params.size(); i++){                  pstmt.setObject(index++, params.get(i));              }          }          resultSet = pstmt.executeQuery();//返回查询结果          ResultSetMetaData metaData = resultSet.getMetaData();          int col_len = metaData.getColumnCount();          while(resultSet.next()){              for(int i=0; i<col_len; i++ ){                  String cols_name = metaData.getColumnName(i+1);                  Object cols_value = resultSet.getObject(cols_name);                  if(cols_value == null){                      cols_value = "";                  }                  map.put(cols_name, cols_value);              }          }          return map;      }  /**查询多条记录      * @param sql      * @param params      * @return      * @throws SQLException      */      public static List<Map<String, Object>> findModeResult(String sql, List<Object> params) throws SQLException{          List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();          int index = 1;          pstmt = connection.prepareStatement(sql);          if(params != null && !params.isEmpty()){              for(int i = 0; i<params.size(); i++){                  pstmt.setObject(index++, params.get(i));              }          }          resultSet = pstmt.executeQuery();          ResultSetMetaData metaData = resultSet.getMetaData();          int cols_len = metaData.getColumnCount();          while(resultSet.next()){              Map<String, Object> map = new HashMap<String, Object>();              for(int i=0; i<cols_len; i++){                  String cols_name = metaData.getColumnName(i+1);                  Object cols_value = resultSet.getObject(cols_name);                  if(cols_value == null){                      cols_value = "";                  }                  map.put(cols_name, cols_value);              }              list.add(map);          }          return list;      }      public static void main(String[] args) throws Exception {    JdbcUtils.initParam("db.properties");JdbcUtils.getConnection();String query = "select * from stock_xsb_company";JdbcUtils.findModeResult(query, null);}}
0 0