关闭

JavaWeb开发基础:JDBC

标签: java webjdbcjava
315人阅读 评论(0) 收藏 举报
分类:

创建数据库和表

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 =  InnoDB
default 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表中的数据
图片1
修改

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();
			//设置事务自动提交为false
			conn.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的记录已存在,无法插入

图片2

查看数据表,Delete被撤销

图片3

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

  • 在src中新建properties,内容为:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/javaweb_db
user=root
password=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;
	}
	@Override
	public 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 {
	@Override
	public 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();
	}

	@Override
	public 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();
	}

	@Override
	public 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();
			}
		}
	}
}

优化后项目包结构

图片4

JDBC源码

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:7238次
    • 积分:192
    • 等级:
    • 排名:千里之外
    • 原创:12篇
    • 转载:0篇
    • 译文:0篇
    • 评论:3条
    文章分类
    最新评论
    文章存档