JavaWeb开发基础：JDBC

来源：互联网发布：女生双肩包推荐知乎编辑：程序博客网时间：2024/05/18 01:10

创建数据库和表

create table user_tbl(id int(11) unsigned not null auto_increment primary key,name varchar(50) not null default "",password varchar(50) not null default "",email varchar(50) default "")engine =  InnoDBdefault charset = utf8;insert into user_tbl(id,name,password,email) values (1,"haha","haha",""),(2,"hehe","hehe","");

连接数据库步骤
加载JDBC驱动，创建数据库链接

private void DBConnection(){       try{            Class.forName(driver);           conn = DriverManager.getConnection(url,user,password);     }catch(Exception e){           e.printStackTrace();      }}

创建Statement，执行sql语句，处理结果

//查询public void select(){      sql = "select * from user_tbl";      try{           DBConnection();           st = conn.createStatement();           //执行查询           rs = st.executeQuery(sql);           while(rs.next()){                  System.out.println(rs.getInt("id")+" "                      +rs.getString("name"));     }     }catch(Exception e){          e.printStackTrace();     }finally{         close();     }}

关闭JDBC对象

public void close(){      try{           if(rs!=null) rs.close();           if(st!=null) st.close();           if(conn!=null) conn.close();      }catch(Exception e){           e.printStackTrace();      }}

完整代码

package com.demo;import java.sql.*;import com.sun.org.apache.regexp.internal.recompile;public class JDBCTest {       private Connection conn = null;       private String sql = "";       private Statement st = null;       private ResultSet rs = null;       //mysql驱动       private String driver = "com.mysql.jdbc.Driver";       //javaweb_db为数据库名       private String url = "jdbc:mysql://localhost:3306/javaweb_db";       private String user = "root";       private String password = "1234";       //获取数据库连接       private void DBConnection(){              try{                   Class.forName(driver);                   conn = DriverManager.getConnection(url,user,password);              }catch(Exception e){                   e.printStackTrace();              }       }       //关闭JDBC对象       public void close(){            try{                  if(rs!=null) rs.close();                  if(st!=null) st.close();                  if(conn!=null) conn.close();            }catch(Exception e){                  e.printStackTrace();            }       }     //查询     public void select(){           sql = "select * from user_tbl";           try{                 DBConnection();                  st = conn.createStatement();                 //执行查询                 rs = st.executeQuery(sql);                 while(rs.next()){                          System.out.println(rs.getInt("id")+" "                                 +rs.getString("name"));                 }           }catch(Exception e){                 e.printStackTrace();           }finally{                  close();           }     }     //插入     public void add(){            sql = "insert into user_tbl(id,name,password,email) values (4,'rrr','rrr','')";            try{                 DBConnection();                 st = conn.createStatement();                        //执行更新                 st.executeUpdate(sql);            }catch(Exception e){                 e.printStackTrace();            }finally{                 close();            }      }      public void update(){}      public void delete(){}      public static void main(String[] args) {             JDBCTest jdbc= new JDBCTest();             // jdbc.select();             jdbc.add();      }}

事务处理

事务是指由一条或多条对数据库更新的sql语句所组成的一个不可分割的工作单元。只有当事务中的所有操作都正常完成了，整个事务才能被提交到数据库，如果有一项操作没有完成，就必须撤消整个事务。在jdbc api中，默认的情况为自动提交事务，也就是说，每一条对数据库的更新的sql语句代表一项事务，操作成功后，系统自动调用commit（）来提交，否则将调用rollback（）来撤消事务。可以通过调用setAutoCommit(false)来禁止自动提交事务。然后就可以把多条更新数据库的sql语句做为一个事务，在所有操作完成之后，调用commit（）来进行整体提交。倘若其中一项sql操作失败，就不会执行commit（）方法，而是产生相应的sqlexception，此时就可以捕获异常代码块中调用rollback（）方法撤消事务。
user_tbl表中的数据

修改

package com.demo;import java.sql.*;public class JDBCTest {private String sql = "";private Statement st = null;private ResultSet rs = null;//mysql驱动private String driver = "com.mysql.jdbc.Driver";//javaweb_db为数据库名private String url = "jdbc:mysql://localhost:3306/javaweb_db";private String user = "root";private String password = "1234";//获取数据库连接private Connection DBConnection(){Connection conn = null;try{Class.forName(driver);conn = DriverManager.getConnection(url,user,password);}catch(Exception e){e.printStackTrace();}return conn;}//关闭JDBC对象public void close(){try{if(rs!=null) rs.close();if(st!=null) st.close();}catch(Exception e){e.printStackTrace();}}//查询public void select(Connection conn){sql = "select * from user_tbl";try{DBConnection();st = conn.createStatement();//执行查询rs = st.executeQuery(sql);while(rs.next()){System.out.println(rs.getInt("id")+" "       +rs.getString("name"));}}catch(Exception e){e.printStackTrace();}finally{close();}}//插入public void add(Connection conn) throws SQLException{sql = "insert into user_tbl(id,name,password,email) values (4,'rrr','rrr','')";st = conn.createStatement();//执行更新st.executeUpdate(sql);close();}public void update(){}//刪除public void delete(Connection conn) throws SQLException{sql = "delete from user_tbl where id = '1'";st = conn.createStatement();//执行更新st.executeUpdate(sql);close();}public static void main(String[] args) {JDBCTest jdbc= new JDBCTest();Connection conn = null;try{conn = jdbc.DBConnection();//设置事务自动提交为falseconn.setAutoCommit(false);jdbc.add(conn);jdbc.delete(conn);//事务提交conn.commit();}catch(Exception e){System.out.println("========捕获异常");e.printStackTrace();try {conn.rollback();System.out.println("========事务回滚成功");} catch (Exception e2) {e2.printStackTrace();}}finally{try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

捕获到异常，id=4的记录已存在，无法插入

查看数据表，Delete被撤销

从属性文件中读取数据库配置

在src中新建properties，内容为：

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/javaweb_dbuser=rootpassword=1234

新建ConnectionFactory类

加载配置文件

Properties properties = new Properties();try {     InputStream in = ConnectionFactory.class.getClassLoader()               .getResourceAsStream("dbconfig.properties");     properties.load(in);} catch (IOException e) {     System.out.println("=====配置文件加载错误=====");}

获取配置属性

driver = properties.getProperty("driver");url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");

实现工厂单例模式

private static final ConnectionFactory factory = new ConnectionFactory();private ConnectionFactory(){}//获得连接工厂实例public static ConnectionFactory getInstance(){      return factory;}

完整代码

package com.db.util;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;public class ConnectionFactory {private static final ConnectionFactory factory = new ConnectionFactory();private Connection conn = null;private static String driver = null;private static String url = null;private static String user = null;private static String password = null;static{//加载配置文件Properties properties = new Properties();try {InputStream in = ConnectionFactory.class.getClassLoader().getResourceAsStream("dbconfig.properties");properties.load(in);} catch (IOException e) {System.out.println("=====配置文件加载错误=====");}//获取配置属性driver = properties.getProperty("driver");url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");}private ConnectionFactory(){}//获得连接工厂实例public static ConnectionFactory getInstance(){return factory;}//获得数据库连接public Connection getConnection(){try {Class.forName(driver);conn = DriverManager.getConnection(url,user,password);} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();}return conn;}}

编写测试类

package com.db.test;import java.sql.Connection;import java.sql.SQLException;import com.db.util.ConnectionFactory;public class ConfigTest {public static void main(String[] args) {Connection conn = ConnectionFactory.getInstance().getConnection();try {System.out.println(conn.getAutoCommit());} catch (SQLException e) {e.printStackTrace();}finally{try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

返回结果为true

结构优化

创建DTO类，对应数据库中的表

package com.db.entity;public abstract class IdEntity {protected long id;public long getId() {return id;}public void setId(long id) {this.id = id;}}package com.db.entity;public class UserEntity extends IdEntity{private String name;private String password;private String email;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}@Overridepublic String toString() {return "UserEntity [name=" + name + ", password=" + password+ ", email=" + email + ", id=" + id + "]";}}

创建DAO接口和实现类，用于访问数据库及增删查改等操作

package com.db.dao;import java.sql.Connection;import java.sql.SQLException;import com.db.entity.UserEntity;public interface UserDao {public void insert(Connection conn, UserEntity user) throws SQLException;public void update(Connection conn, UserEntity user) throws SQLException;public void delete(Connection conn, UserEntity user) throws SQLException;}package com.db.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import com.db.dao.UserDao;import com.db.entity.UserEntity;public class UserDaoImpl implements UserDao {@Overridepublic void insert(Connection conn, UserEntity user) throws SQLException {String sql = "insert into user_tbl(name,password,email) values (?,?,?)";PreparedStatement ptmt = conn.prepareCall(sql);//设置占位符参数,从1开始ptmt.setString(1, user.getName());ptmt.setString(2, user.getPassword());ptmt.setString(3, user.getEmail());//执行ptmt.execute();ptmt.close();}@Overridepublic void update(Connection conn, UserEntity user) throws SQLException {String sql = "update user_tbl set name=?, password=?, email=? where id=?";PreparedStatement ptmt = conn.prepareCall(sql);//设置占位符参数,从1开始ptmt.setString(1, user.getName());ptmt.setString(2, user.getPassword());ptmt.setString(3, user.getEmail());ptmt.setLong(4, user.getId());//执行ptmt.execute();ptmt.close();}@Overridepublic void delete(Connection conn, UserEntity user) throws SQLException {String sql = "delete from user_tbl where id=?";PreparedStatement ptmt = conn.prepareCall(sql);//设置占位符参数,从1开始ptmt.setLong(1, user.getId());//执行ptmt.execute();ptmt.close();}}

编写测试类

package com.db.test;import java.sql.Connection;import java.sql.SQLException;import com.db.dao.UserDao;import com.db.dao.impl.UserDaoImpl;import com.db.entity.UserEntity;import com.db.util.ConnectionFactory;public class UserDaoTest {public static void main(String[] args) {UserEntity user = new UserEntity();user.setId(1);user.setName("xixi");user.setPassword("123456");user.setEmail("1234@qq.com");Connection conn = null;try {conn = ConnectionFactory.getInstance().getConnection();conn.setAutoCommit(false);UserDao userDao = new UserDaoImpl();userDao.update(conn, user);//userDao.delete(conn, user);//userDao.insert(conn, user);conn.commit();} catch (SQLException e) {e.printStackTrace();try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}}finally{try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

优化后项目包结构

JDBC源码

0 0