java之jdbc入门笔记2

注意导包：mysql-connector-java-5.1.39-bin.jar

配置文件：jdbc.properties(放在src下)

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/webstudy
user=root
password=123

jdbc的工具类

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class JdbcUtil {
	//用于加载配置文件为properties快速的工具，避免繁杂的IO操作
	 static ResourceBundle bundle;
	 static String url;
	 static String user;
	 static String password;
	static{
		 bundle = ResourceBundle.getBundle("jdbc");
		 url = bundle.getString("url");
		 user= bundle.getString("user");
		 password= bundle.getString("password");
		 
		 try {
			 //加载驱动
			Class.forName(bundle.getString("driverClass"));
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 获取连接
	 * */
	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 关闭资源
	 * */
	public static void closeResource(Connection conn,PreparedStatement stm,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				rs=null;
			}
		}
		if(stm!=null){
			try {
				stm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				stm=null;
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				conn=null;
			}
		}
	}
	
}

jdbc案例：curd

package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;


import java.sql.ResultSet;

import org.junit.After;
import org.junit.Test;
/**
 * 数据库的curd
 * */
public class JdbcTest {
	@Test
	public void read(){
		Connection conn;
		PreparedStatement stm;
		ResultSet rs;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "select * from t_user";
			stm=conn.prepareStatement(sql);
			rs=stm.executeQuery();
			while(rs.next()){
				//通过数据库字段名获取值
				System.out.println("cid:"+rs.getInt("uid")+" username:"+rs.getString("username"));
			}
			JdbcUtil.closeResource(conn, stm, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	@Test
	public void update(){
		Connection conn;
		PreparedStatement stm;
		int rs;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "insert into t_user values(?,?)";
			stm=conn.prepareStatement(sql);
			//设置sql中的参数
			stm.setInt(1, 3);
			stm.setString(2, "芈月");
			rs = stm.executeUpdate();//返回数据执行操作后的影响行数，插入成功返回大于0的数
			if(rs>0){
				System.out.println("success");
			}else{
				System.out.println("false");
			}
			JdbcUtil.closeResource(conn, stm, null);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	@Test
	public void change(){
		Connection conn;
		PreparedStatement stm;
		int rs;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "update t_user set username=? where uid=?";
			stm=conn.prepareStatement(sql);
			stm.setInt(2, 3);
			stm.setString(1, "宫本");
			rs = stm.executeUpdate();//返回数据执行操作后的影响行数。
			if(rs>0){
				System.out.println("success");
			}else{
				System.out.println("false");
			}
			JdbcUtil.closeResource(conn, stm, null);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	@Test
	public void delete(){
		Connection conn;
		PreparedStatement stm;
		int rs;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "delete from t_user where uid=?";
			stm=conn.prepareStatement(sql);
			stm.setInt(1, 3);
			rs = stm.executeUpdate();//返回数据执行操作后的影响行数。
			if(rs>0){
				System.out.println("success");
			}else{
				System.out.println("false");
			}
			JdbcUtil.closeResource(conn, stm, null);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
}