java jdbc insert sql_使用JDBC执行 select insert update delete

[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的对象

}

}

}

}

}