大型项目中Java连接MSSQL的性能优化

//加载数据库驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//连接数据库
String url = "jdbc:sqlserver://localhost:1433;databasename=example";
String username = "username";
String password = "password";
Connection conn = DriverManager.getConnection(url, username, password);
//创建Statement对象
Statement statement = conn.createStatement();
//查询并处理结果集
String query = "SELECT * FROM table";
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
  //处理查询结果
}
//释放资源
rs.close();
statement.close();
conn.close();

<c3p0-config>
  <default-config>
    <property name="driverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
    <property name="jdbcUrl">jdbc:sqlserver://localhost:1433;databasename=example</property>
    <property name="user">username</property>
    <property name="password">password</property>
    <property name="acquireIncrement">5</property>
    <property name="initialPoolSize">10</property>
    <property name="minPoolSize">5</property>
    <property name="maxPoolSize">20</property>
    <property name="maxStatements">100</property>
    <property name="numHelperThreads">5</property>
  </default-config>
</c3p0-config>

Connection conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false); //关闭自动提交
Statement statement = conn.createStatement();
try {
  statement.executeUpdate("INSERT INTO table (column1, column2) VALUES (value1, value2)");
  statement.executeUpdate("INSERT INTO table (column1, column2) VALUES (value3, value4)");
  conn.commit(); //提交事务
} catch (SQLException e) {
  conn.rollback(); //回滚事务
} finally {
  statement.close();
  conn.close();
}

//关闭自动提交
conn.setAutoCommit(false);
//创建Statement对象
Statement statement = conn.createStatement();
//添加批处理语句
statement.addBatch("INSERT INTO table (column1, column2) VALUES (value1, value2)");
statement.addBatch("INSERT INTO table (column1, column2) VALUES (value3, value4)");
//执行批处理并提交
statement.executeBatch();
conn.commit();

String query = "SELECT * FROM table WHERE column1=?";
PreparedStatement ps = conn.prepareStatement(query);
ps.setString(1, "value");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
  //处理查询结果
}
rs.close();
ps.close();

CREATE INDEX index_name ON table (column1, column2);