1,将增删查改语句直接写到一个工具类的代码
工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
//查询方法
public static void query(String sql,IRowMapper rowMapper) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
rowMapper.rowMapper(resultSet);//多态
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (resultSet!=null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (statement!=null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection!=null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//增删改方法
public static boolean update(String sql) {
Connection connection = null;
Statement statement= null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
statement= connection.createStatement();
return statement.executeUpdate(sql)>0;
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (statement!=null) {
statement.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if (connection !=null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
}
接口类
interface IRowMapper{
void rowMapper(ResultSet rs);
}
查询类中代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Select {
public static void main(String[] args) {
String sql="select * from student";
class RowMapper implements IRowMapper{
@Override
public void rowMapper(ResultSet st) {
try {
while(st.next()) {
String id=st.getString("id");
String studentName=st.getString("student_name");
String address=st.getString("address");
String mobile=st.getString("mobile");
System.out.println(id+":"+studentName+":"+address+":"+mobile);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper=new RowMapper(); //2,实现类对象
DBUtil.query(sql,rowMapper);
}
}
Update类中代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Update {
public static void main(String[] args) {
String sql="delete from student where student_name='tom'";
if(DBUtil.update(sql)) { //update(sql)返回一个boolean类型的值,可用在if条件中。
System.out.println("ok");
}else {
System.out.println("no");
}
}
}
2,将工具类中代码优化
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");//1、加载驱动:因为驱动只需要加载一次,所以定义在静态代码块中,只在类加载时加载一次驱动。
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static Connection getConnection() { //2、获取连接:将连接定义为一个方法,实现代码的复用。
try {
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void query(String sql,IRowMapper rowMapper) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection(); //调用连接方法
statement = connection.createStatement(); //3、创建语句
resultSet = statement.executeQuery(sql); //4、执行SQL
rowMapper.rowMapper(resultSet); //5、处理结果
} catch (Exception e) {
e.printStackTrace();
}finally {
close(resultSet,statement,connection); //6、释放资源:将关闭代码定义为一个方法,实现代码的复用。
}
}
public static boolean update(String sql) {
Connection connection = null;
Statement statement= null;
try {
connection = getConnection();
statement= connection.createStatement(); //3、创建语句
return statement.executeUpdate(sql)>0; //4、执行SQL 和 5、处理结果
} catch (Exception e) {
e.printStackTrace();
}finally {
close(statement,connection); //6、释放资源
}
return false;
}
//构造方法的重载
private static void close(Statement statement,Connection connection) {
try {
if (statement!=null) {
statement.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
if (connection !=null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void close(ResultSet resultSet,Statement statement,Connection connection) {
try {
if (resultSet!=null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
close(statement,connection); //调用上一个构造方法,代码优化。
}
}