[java]代码库package demo.mysql;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import demo.utils.JDBCUtils;
/*
使用JDBC执行 select insert update delete
*/
public class Demo1 {
@Test
public void testSelect() throws Exception{
//注册驱动(数据库的类型)
/*
* 这种方式存在的问题:
* 1. 依赖于jar包
* 2. 导致驱动com.mysql.jdbc.Driver注册两次
*/
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//实际开发,应该采用下面的方式注册驱动 --> Java反射
Class.forName("com.mysql.jdbc.Driver");
//获取连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "password";
Connection conn = DriverManager.getConnection(url, user, password);
//得到运行环境
Statement st = conn.createStatement();
//执行SQL
String sql = "select * from myuser";
ResultSet rs = st.executeQuery(sql);
//处理结果
while(rs.next()){
//每次循环,都取出一条记录
int userID = rs.getInt("userID");
String name = rs.getString("userName");
String userPass = rs.getString("userPass");
String email = rs.getString("email");
Date birthday = rs.getDate("birthday");
System.out.println(userID);
System.out.println(name);
System.out.println(userPass);
System.out.println(email);
System.out.println(birthday);
System.out.println("*********************");
}
//释放资源
rs.close();
st.close();
conn.close();
}
@Test
public void testInsert(){
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "password";
Connection conn = null;
Statement st = null;
try{
//注册驱动(数据库的类型)
Class.forName("com.mysql.jdbc.Driver");
//获取连接
conn = DriverManager.getConnection(url, user, password);
//得到运行环境
st = conn.createStatement();
//执行SQL
String sql = "insert into myuser(userID,userName) values("+5+",'Tom'"+")";
System.out.println("sql="+sql);
int result = st.executeUpdate(sql);
//处理结果
if(result>0){
System.out.println("操作成功");
}else{
System.out.println("操作失败");
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
//释放资源
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;//--> 让他迅速成为java gc的对象
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;//--> 让他迅速成为java gc的对象
}
}
}
}
@Test
public void testUpate(){
String sql = "update myuser set username='Tom_AB' where userID=4";
Connection conn = null;
Statement st = null;
try {
//从工具类中获取连接
conn = JDBCUtils.getConnection();
st = conn.createStatement();
int result = st.executeUpdate(sql);
//处理结果
if(result>0){
System.out.println("操作成功");
}else{
System.out.println("操作失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//调用工具类释放资源
JDBCUtils.release(conn, st, null);
}
}
@Test
public void testDelete(){
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "password";
Connection conn = null;
Statement st = null;
try{
//注册驱动(数据库的类型)
Class.forName("com.mysql.jdbc.Driver");
//获取连接
conn = DriverManager.getConnection(url, user, password);
//得到运行环境
st = conn.createStatement();
//执行SQL
String sql = "delete from myuser where userID=5";
System.out.println("sql="+sql);
int result = st.executeUpdate(sql);
//处理结果
if(result>0){
System.out.println("操作成功");
}else{
System.out.println("操作失败");
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
//释放资源
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;//--> 让他迅速成为java gc的对象
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;//--> 让他迅速成为java gc的对象
}
}
}
}
}