Java基础学习记录之JDBC完成CRUD

这是JDBC进行单表的CRUD,为了减少代码的重复度，添加jdbcUtil工具类，完成Connection的获取和资源的释放。为了方便数据库相关的修改，添加jdbc.cfg.properties文件，

1.数据库：

create database basketball;

use basketball;

create table star(
	id int primary key auto_increment,
	name varchar(40),
	password varchar(40),
	email varchar(60),
	birthday date
);

insert into users(name,password,email,birthday) values('James','LeBron','LeBron@sina.com','1984-12-30');
insert into users(name,password,email,birthday) values('Wede','Dwyane','Dwyane@sina.com','1982-1-17');
insert into users(name,password,email,birthday) values('Bosh','Chris','Chris@sina.com','1984-3-24');
insert into users(name,password,email,birthday) values('Bryant','Kobe','Kobe@sina.com','1978-08-23');

2.jdbc.cfg.properties:

root：是数据库的用户名，hello：是数据库的密码。

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:///basketball
user=root
password=hello

3.jdbcUtil.java:

可以获取connection和释放资源。

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtil {
	private static String driverClass;
	private static String url;
	private static String user;
	private static String password;

	static{
		try {
			
			InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.cfg.properties");
			Properties pro = new Properties();
			pro.load(is);
			
			driverClass = pro.getProperty("driverClass");
			url = pro.getProperty("url");
			user = pro.getProperty("user");
			password = pro.getProperty("password");
			Class.forName(driverClass);
		} catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}	
	}
	
	public static Connection getConnection() throws Exception{
		Connection con = DriverManager.getConnection(url,user,password);
		return con;
	}
	
	public static void release(ResultSet rs,Statement stt,Connection con){
		if(rs!=null){
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			rs = null;
		}
		if(stt != null){
			try{
				stt.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			stt = null;
		}
		if(con != null){
			try{
				con.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
			con = null;
		}
	}
}

4.User.java：

import java.util.Date;
public class User {
	private int id;
	private String name;
	private String password;
	private String email;
	private Date birthday;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	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;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password
				+ ", email=" + email + ", birthday=" + birthday + "]";
	}
	
	
}

5.CRUD：

测试时可以单独放开测试方法。

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import test.domain.User;
import test.jdbcUtil.JdbcUtil;
public class JdbcCRUD {

	public static void main(String[] args) {
//		添加
//		testAdd();
		
//		修改
//		testUpdate();
		
//		删除
//		testDelete();
		
//		查找
//		testFind();
		
		
	}

	public static void testAdd(){
		Connection con = null;
		Statement stt = null;
		ResultSet rs = null;
		
		try {
			con = JdbcUtil.getConnection();
			stt = con.createStatement();
			int num = stt.executeUpdate("insert into users(name,password,email,birthday) values('Irving','Kyrie','Kyrie@sina.com','1992-3-23')");
			if(num != 0){
				System.out.println("添加数据成功");
			}
		} catch (Exception e) {
			
			throw new RuntimeException(e);
			
		}finally{
			
			JdbcUtil.release(rs, stt, con);
		}
	}
	
	public static void testUpdate() {
		Connection con = null;
		Statement stt = null;
		ResultSet rs = null;
		try {
			con = JdbcUtil.getConnection();
			stt = con.createStatement();
			int num = stt.executeUpdate("update users set email='lakers@126.com' where id=4");
			if(num != 0){
				System.out.println("修改数据成功");
			}
		} catch (Exception e) {
			throw new RuntimeException();
		}finally{
			JdbcUtil.release(rs, stt, con);
		}
		
	}
	
	public static void testDelete() {
		Connection con = null;
		Statement stt = null;
		ResultSet rs = null;
		
		try {
			con = JdbcUtil.getConnection();
			stt = con.createStatement();
			int num = stt.executeUpdate("delete from users where id = 2");
			if(num != 0){
				System.out.println("删除数据成功");
			}
		} catch (Exception e) {
			throw new RuntimeException();
		}finally{
			JdbcUtil.release(rs, stt, con);
		}
		
	}
	
	public static void testFind() {
		Connection con = null;
		Statement stt = null;
		ResultSet rs = null;
		
		try {
			con = JdbcUtil.getConnection();
			stt = con.createStatement();
			rs = stt.executeQuery("select * from users");
			if(rs.next()){
				System.out.println(rs.getString("name"));
			}
		} catch (Exception e) {
			throw new RuntimeException();
		}finally{
			JdbcUtil.release(rs, stt, con);
		}
	}

	
}