JDBC 笔记2 封装

1 封装

         新建类 DBConnUtil  ，新建database.properties 文件储存链接信息如下所示

        

jdbcDriver=com.mysql.jdbc.Driver
jdbcUrl=jdbc\:mysql\://localhost\:3306/homework
jdbcUser=root
jdbcPasswd=123

public class DBConnUtil {

private static String jdbcDriver = "";  //定义连接信息
private static String jdbcUrl = "";

private static String jdbcUser = "";

private static String jdbcPasswd = "";

static{
InputStream is = null;
try {
is = DBConnUtil.class.getClassLoader().getResourceAsStream("database.properties");//加载database.properties文件
Properties p = new Properties();
p.load(is);
jdbcDriver = p.getProperty("jdbcDriver"); //赋值
jdbcUrl = p.getProperty("jdbcUrl");
jdbcUser = p.getProperty("jdbcUser");
jdbcPasswd = p.getProperty("jdbcPasswd");

} catch (IOException e) {
e.printStackTrace();
} finally {
if(is != null){
try {
is.close();//  关闭is
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

public static Connection getConn(){// 建立连接方法
Connection conn = null;
try {
Class.forName(jdbcDriver);
conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPasswd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
retu conn;
}

public static void closeAll(ResultSet rs ,Statement st,Connection conn){  //关闭连接（用于增删改）
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static void closeAll(ResultSet rs ,PreparedStatement ps,Connection conn){  // 关闭连接（用于查）
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

}

 

2 测试增删改差

　　2.1 插入数据

public boolean ChaRu1(User user){
boolean flag=true;
Connection conn=null;
Statement st=null;
String sql="insert into user (name,pwd) values('"+user.getName()+"','"+user.getPwd()+"')";
conn=DBConnUtil.getConn();// getConn()方法是静态的，直接用类调用建立连接。
try {
st=conn.createStatement();
int i=st.executeUpdate(sql);
if(i==0){
flag=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnUtil.closeAll(null, st, conn);//关闭连接，由于插入操作不涉及ResultSet类，故其对象rs无需关闭，用null代替。
}
retu flag;
}

 

　　2.2 修改数据

public boolean XiuGai2(User user){
boolean flag=true;
Connection conn =null;
Statement st=null;
String sql="update user set pwd='"+user.getPwd()+"' where name='"+user.getName()+"'";
conn=DBConnUtil.getConn();
try {
st=conn.createStatement();
st.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnUtil.closeAll(null, st, conn);
}
retu flag;
}

 

　　2.3 删除数据

public boolean ShanChu2(int id){
boolean flag=true;
Connection conn=null;
Statement st=null;
String sql="delete from user where id="+id;
conn=DBConnUtil.getConn();
try {
st=conn.createStatement();
int i=st.executeUpdate(sql);
if(i==0){
flag=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnUtil.closeAll(null, st, conn);
}

retu flag;
}

 

　　2.4删除数据

public List<User> ChanKan2(){
List<User> list= new ArrayList<User>();
Connection conn=null;
Statement st= null;
ResultSet rs=null;
String sql="select * from user";
conn=DBConnUtil.getConn();
try {
st=conn.createStatement();
rs=st.executeQuery(sql);
while(rs.next()){
User user=new User();
user.setName(rs.getString("name"));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnUtil.closeAll(rs, st, conn);
}
retu list;
}