###编写数据库连接类###
一、连接类JDBCUtil
public class JDBCUtil {
//使用静态代码块加载驱动
static{
try{
String driverName="com.mysql.jdbc.Driver";
Class.forName(driverName);
}catch(ClassNotFoundException e){
System.out.println("加载驱动异常");
e.printStackTrace();
}
}
//提供连接方法
public static Connection getConnection(){
Connection con=null;
try{
String DB="jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf-8";
con=DriverManager.getConnection(DB,"name","pwd");
}catch(Exception e){
e.printStackTrace();
}
return con;
}
//关闭连接方法
public static void close(ResultSet rs,Statement stmt,Connection con){
try{
if(rs!=null)
rs.close();
}catch(Exception ex){
ex.printStackTrace();
}
try{
if(stmt!=null)
stmt.close();
}catch(Exception ex){
ex.printStackTrace();
}
try{
if(con!=null)
con.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}
二、测试类Test
public class Test {
Connection con;
Statement stmt;
public Test(Connection con) {
this.con = con;
try{
stmt=con.createStatement();
}catch(Exception e){
e.printStackTrace();
}
}
ResultSet rs;
public Connection getCon() {
return con;
}
public Statement getStmt() {
return stmt;
}
public ResultSet getRs() {
return rs;
}
public void createTable() throws SQLException{
stmt.executeUpdate("DROP TABLE IF EXISTS jdbc_test");
String sql="create table jdbc_test(id int,name varchar(30))";
stmt.executeUpdate(sql);
System.out.println("创建jdbc_test表完成");
}
public void insert()throws SQLException{
String sql1="insert into jdbc_test value(1,'tom')";
String sql2="insert into jdbc_test value(2,'mac')";
String sql3="insert into jdbc_test value(3,'awp')";
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
int[] result=stmt.executeBatch();
for(int i=0;i<result.length;i++){
System.out.println("第"+(i+1)+"次插入返回"+result[0]+"条结果");
}
}
public void select() throws SQLException{
String sql="select id,name from jdbc_test";
rs=stmt.executeQuery(sql);
System.out.println("---数据库查询---");
System.out.println("id\tname");
System.out.println("-------------------------");
while(rs.next()){
String id=rs.getString("id");
String name=rs.getString("name");
System.out.println(id+"\t"+name+"\n");
}
}
public static void main(String[] args) {
// TODO 自动生成的方法存根
Connection con=JDBCUtil.getConnection();
Test dao=new Test(con);
try{
dao.createTable();
dao.insert();
dao.select();
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtil.close(dao.getRs(), dao.getStmt(), dao.getCon());
}
}
}
注:throws关键字是方法的异常抛出声明,throw用于抛出异常对象的实例
mysql 查询记录数
依次查询的性能变优:
1、count(字段):遍历整张表
2、count(主键):遍历整张表,每行id都取出
3、count(1):遍历整张表,每行放入“1”
4、count(*):优化后(最优)