当前位置: 代码迷 >> 综合 >> 通过DBUtils直接操作插入MySql数据库并且将执行的sql写入到指定的文件夹
  详细解决方案

通过DBUtils直接操作插入MySql数据库并且将执行的sql写入到指定的文件夹

热度:64   发布时间:2024-01-18 03:21:24.0

DBUtils工具类:

package com.example.demo;import java.lang.reflect.Field;
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 DBUtils {private static String USERNAME = "root";// 定义数据库的密码private static String PASSWORD = "root";// 定义数据库的驱动信息private static String DRIVER = "com.mysql.jdbc.Driver";// 定义访问数据库的地址private static String URL = "jdbc:mysql://localhost:3306/demo";private static DBUtils per = null;// 定义数据库的链接private Connection con = null;// 定义sql语句的执行对象private PreparedStatement pstmt = null;// 定义查询返回的结果集合private ResultSet resultSet = null;private DBUtils() {}/*** 单例模式,获得工具类的一个对象* * @return*/public static DBUtils getInstance() {if (per == null) {per = new DBUtils();per.registeredDriver();}return per;}private void registeredDriver() {// TODO Auto-generated method stubtry {Class.forName(DRIVER);System.out.println("注册驱动成功!");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/*** 获得数据库的连接* * @return*/public Connection getConnection() {try {con = DriverManager.getConnection(URL, USERNAME, PASSWORD);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}//System.out.println("连接数据库成功!!");return con;}/*** 完成对数据库的表的添加删除和修改的操作* * @param sql* @param params* @return* @throws SQLException*/public boolean executeUpdate(String sql, List<Object> params) throws SQLException {boolean flag = false;int result = -1; // 表示当用户执行添加删除和修改的时候所影响数据库的行数pstmt = con.prepareStatement(sql);if (params != null && !params.isEmpty()) {int index = 1;for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, i);}}result = pstmt.executeUpdate();flag = result > 0 ? true : false;return flag;}/*** 从数据库中查询数据* * @param sql* @param params* @return* @throws SQLException*/public List<Map<String, Object>> executeQuery(String sql, List<Object> params) throws SQLException {List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();int index = 1;pstmt = con.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;}/*** jdbc的封装可以用反射机制来封装,把从数据库中获取的数据封装到一个类的对象里* * @param sql* @param params* @param cls* @return* @throws Exception*/public <T> List<T> executeQueryByRef(String sql, List<Object> params, Class<T> cls) throws Exception {List<T> list = new ArrayList<T>();int index = 1;pstmt = con.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()) {T resultObject = cls.newInstance(); // 通过反射机制创建实例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 = "";}Field field = cls.getDeclaredField(cols_name);field.setAccessible(true); // 打开javabean的访问private权限field.set(resultObject, cols_value);}list.add(resultObject);}return list;}public void closeDB() {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (pstmt != null) {try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (con != null) {try {con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

运行测试:

package com.example.demo;import java.io.PrintWriter;
import java.util.List;
import java.util.Random;import com.avic.user.entity.User;public class Test {public static void main(String[] args) throws Exception {PrintWriter p1 = new PrintWriter("e://demo//2018822//us_user.txt");PrintWriter p2 = new PrintWriter("e://demo//2018822//us_passwrd.txt");PrintWriter p3 = new PrintWriter("e://demo//2018822//us_account.txt");PrintWriter p4 = new PrintWriter("e://demo//2018822//us_user_detail.txt");DBUtils regionUtils = DBUtils.getInstance();regionUtils.getConnection();String[] mobiles = { "177777777777", "18888888888", "19999999999" };for (int i = 0; i < mobiles.length; i++) {// 插入userStringBuffer userbuf = new StringBuffer();userbuf.append("INSERT INTO `us_user` VALUES (");userbuf.append("null").append(",");userbuf.append(mobiles[i]).append(",");userbuf.append("null").append(",'");userbuf.append(createDefulatUserName()).append("',");userbuf.append("now()").append(",");userbuf.append("1").append(",");userbuf.append("0").append(",");userbuf.append("0").append(",");userbuf.append("0").append(",");userbuf.append("0").append(",");userbuf.append("null").append(",");userbuf.append("0").append(",");userbuf.append("null").append(",");userbuf.append("null").append(",");userbuf.append("null").append(");");regionUtils.executeUpdate(userbuf.toString(), null);System.out.println(userbuf.toString());p1.write(userbuf.toString());p1.write("\r\n");}// 插入密码表for (int i = 0; i < mobiles.length; i++) {String sql2 = "INSERT INTO `us_password` VALUES (null,(SELECT u.user_id from us_user u where u.mobile='"+ mobiles[i] + "'), '0', '" + mobiles[i] + "', '123456', null);";regionUtils.executeUpdate(sql2.toString(), null);String sql5 = "INSERT INTO `us_password` VALUES (null,(SELECT u.user_id from us_user u where u.mobile='"+ mobiles[i] + "'), '0', (SELECT u.user_name from us_user u where u.mobile='" + mobiles[i]+ "'), '123456', null);";regionUtils.executeUpdate(sql5.toString(), null);System.out.println(sql5.toString());p2.write(sql2);p2.write("\r\n");String sql3 = "INSERT INTO `us_account` VALUES ((SELECT u.user_id from us_user u where u.mobile='"+ mobiles[i] + "'),0.01,0.00,0,0,0.00);";regionUtils.executeUpdate(sql3.toString(), null);System.out.println(sql3.toString());p3.write(sql3);p3.write("\r\n");String sql4 = "INSERT INTO `us_user_detail` VALUES ((SELECT u.user_id from us_user u where u.mobile='"+ mobiles[i]+ "'),0,null,null,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);";regionUtils.executeUpdate(sql4.toString(), null);System.out.println(sql4);p4.write(sql4);p4.write("\r\n");}p1.flush();p1.close();p2.flush();p2.close();p3.flush();p3.close();p4.flush();p4.close();}private static String createDefulatUserName() {String userName = null;try {DBUtils regionUtils = DBUtils.getInstance();regionUtils.getConnection();String code = "123456789ABCDEFGHIJKLMNOPQSTUVWXYZ";StringBuffer checkCode = new StringBuffer();userName = "";Random ran = new Random();for (int i = 0; i < 5; i++) {checkCode.append(code.charAt(ran.nextInt(code.length())));}userName = "RR" + checkCode.toString();String sql = "select * from us_user where user_name='" + userName + "'";List<User> users = regionUtils.executeQueryByRef(sql, null, User.class);if (users.size() > 0) {userName = createDefulatUserName();return userName;}} catch (Exception e) {e.printStackTrace();}return userName;}
}

 

  相关解决方案