vlambda博客
学习文章列表

/** * MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL（URL 需要显示不建立 SSL，还要设置 CST） */static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";// url 格式：jdbc:mysql://ipAddress:Port/databaseName?useSSL=false&serverTimezone=UTCstatic final String DB_URL = "jdbc:mysql://192.168.42.19:3306/tesjdbc?useSSL=false&serverTimezone=UTC";
static final String USER = "root";static final String PWD = "sa";

public static boolean insertUserInfo(ArrayList<TestModel> list) { Connection connection = null; Statement statement = null; PreparedStatement pres = null; try { Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DB_URL, USER, PWD); if (connection == null) { return false; } statement = connection.createStatement(); if (statement == null) { return false; } // 设置自动提交 false connection.setAutoCommit(false);
 ArrayList<String> sqls = new ArrayList<String>(); for (TestModel model : list) { // 注意 StringBuffer 与 StringBuilder 用法，前者线程安全，后者不是线程安全，适用于单线程// StringBuffer sql = new StringBuffer();// sql.append("insert into user(name,age) values(");// sql.append(model.getName());// sql.append(",");// sql.append(model.getAge());// sql.append(")");// sqls.add(sql.toString());
 String sqlString = "insert into user(name,age) values(?,?)"; // 使用事务 pres = connection.prepareStatement("insert into user(name,age) values (?,?)"); pres.setObject(1, model.getName()); pres.setObject(2, model.getAge()); pres.execute(); } // commit connection.commit();
 // close pres.close(); statement.close(); connection.close();
 } catch (Exception e) { System.out.println(e.getStackTrace()); try { connection.rollback(); if (pres != null) { pres.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } catch (Exception e1) { System.out.println(e1.getStackTrace()); } } return true;    }

 public static boolean deleteUserInfo(String name) { Connection connection = null; Statement statement = null; try { Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DB_URL, USER, PWD); if (connection == null) { return false; } statement = connection.createStatement(); if (statement == null) { return false; } //boolean success = statement.execute("delete from user where name =" + "'"+name+"'"); int result = statement.executeUpdate("delete from user where name =" + "'"+name+"'"); statement.close(); connection.close(); return result>0?true:false;
 } catch (Exception e) { System.out.println(e.getStackTrace()); try { if (connection != null) { connection.close(); } if (statement != null) { statement.close(); } return false; } catch (Exception e1) { System.out.println(e1.getStackTrace()); return false; } }}

public static boolean updateUserInfo(TestModel model) { Connection connection = null; PreparedStatement pres = null; try { Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DB_URL,USER,PWD); connection.setAutoCommit(false); if (connection == null) { return false; }
 String sql = "update user set user.age=? where user.name=?"; pres = connection.prepareStatement(sql); pres.setObject(1, model.getAge()); pres.setObject(2, model.getName());
 pres.execute(); connection.commit();
 pres.close(); connection.close(); } catch (Exception e) { System.out.println(e.getStackTrace()); try { connection.rollback(); if (pres != null) { pres.close(); } if (connection != null) { connection.close(); } System.out.println("更新用户信息失败"); return false; } catch (Exception e1) { System.out.println("更新用户信息失败"); return false; } } return true;}

public static List<TestModel> getUserInfo() { Connection conn = null; Statement state = null; ArrayList<TestModel> modelArrayList = null; try { // register jdbc driver Class.forName(JDBC_DRIVER);
 // open database System.out.println("连接数据库..."); conn = DriverManager.getConnection(DB_URL, USER, PWD); if (conn == null) { System.out.println("数据库连接失败"); return modelArrayList; }
 System.out.println("实例化 Statement 对象"); // execute query sql state = conn.createStatement(); if (state == null) { System.out.println("实例化 Statement 对象失败"); } String sqlString = "select name,age from user"; ResultSet set = state.executeQuery(sqlString); // reade data to list modelArrayList = new ArrayList<TestModel>(); while (set.next()) { TestModel model = new TestModel(); model.setName(set.getString("name")); model.setAge(set.getInt("age")); modelArrayList.add(model); } set.close(); state.close(); conn.close(); } catch (Exception e) { System.out.println(e.getStackTrace()); } finally { try { if (state != null) { state.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { } } System.out.println("执行成功！"); return modelArrayList;}