Demo2.java
package com.cn.statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import com.cn.Util.JdbcUtil;
/**
* Author:Liu Zhiyong(QQ:1012421396)
* Version:Version_1
* Date:2017年3月17日11:01:28
* Desc:使用Statement执行sql语句
1.使用jdbc创建一张员工表
员工表字段:编号,姓名,性别,年龄,职位,邮箱,电话
2.使用jdbc对员工表执行以下操作:
1)插入一条数据
2)修改一条数据
3)删除一条数据
4)查询并打印所有员工数据
*/
public class Demo2 {
private String url = "jdbc:mysql://localhost:3306/mydb";
private String user = "root";
private String password = "root";
/**
* 使用Statement执行DDL语句
*/
@Test
public void test1(){
Connection conn = null;
Statement stmt = null;
try {
//1.注册MySQL驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
conn = DriverManager.getConnection(url, user, password);
//3.创建StateMent
stmt = conn.createStatement();
//4.准备sql
String sql = "create table employee(eid int primary key auto_increment, ename varchar(20), gender varchar(2), dept varchar(20), email varchar(30), phone varchar(20) )";
//5.执行sql,并返回结果
int count = stmt.executeUpdate(sql);
//6.输出结果
System.out.println(count);
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
//7.关闭资源(顺序:后打开,先关闭)
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
System.out.println("Statement关闭失败!");
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("Connection关闭失败!");
throw new RuntimeException(e);
}
}
}
}
/**
*
* 使用Statement执行DML语句
*
* 插入insert
*/
@Test
public void test2() {
Connection conn = null;
Statement stmt = null;
try {
/**
* 抽取代码
* 通过工具获取连接对象
*/
conn = JdbcUtil.getConnection();
//3.创建Statement对象
stmt = conn.createStatement();
//4.准备sql
String sql = "INSERT INTO employee(ename, gender, dept,email,phone) VALUES('刘先森', '男', '开发部', '1012421396@qq.com', '18071897425')";
//5.执行sql
int count = stmt.executeUpdate(sql);
System.out.println(count);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, stmt);
}
}
/**
* 使用Statement执行DML语句
*
* 修改update
*/
@Test
public void test3() {
Connection conn = null;
Statement stmt = null;
//模拟用户输入
String name = "张馨予";
int id = 3;
try {
//通过工具获取连接对象
conn = JdbcUtil.getConnection();
//3.创建Statement对象
stmt = conn.createStatement();
//4.准备sql
String sql = "UPDATE employee SET ename='" + name + "' WHERE eid = " + id;
//5.执行sql
int count = stmt.executeUpdate(sql);
System.out.println(count);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, stmt);
}
}
/**
* 使用Statement执行DML语句
*
* 删除delete
*/
@Test
public void test4() {
Connection conn = null;
Statement stmt = null;
//模拟用户输入
int id = 6;
try {
//通过工具获取连接对象
conn = JdbcUtil.getConnection();
//3.创建Statement对象
stmt = conn.createStatement();
//4.准备sql
String sql = "delete from employee WHERE eid = " + id;
//5.执行sql
int count = stmt.executeUpdate(sql);
System.out.println(count);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, stmt);
}
}
/**
* 使用Statement执行DQL语句(查询操作)
*/
@Test
public void test5() {
Connection conn = null;
Statement stmt = null;
try {
//获取连接对象
conn = JdbcUtil.getConnection();
//创建Statement对象
stmt = conn.createStatement();
//准备sql语句
String sql = "select * from employee";
//执行sql
ResultSet rs = stmt.executeQuery(sql);
System.out.println("根据列的索引取值");
while(rs.next()){
System.out.println(rs.getInt(1) + "#" + rs.getString(2) + "#" + rs.getString(3) + "#" + rs.getString(4) + "#" + rs.getString(5) + "#" + rs.getString(6));
}
System.out.println("根据列的名称取值");
rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt("eid") + "#" + rs.getString("ename") + "#" + rs.getString("gender") + "#" + rs.getString("dept") + "#" + rs.getString("email") + "#" + rs.getString("phone"));
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, stmt);
}
}
}
抽取jdbc获取Connection对象和关闭Connection对象和Statement对象的工具类
JdbcUtil.java
package com.cn.Util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* jdbc的工具类
* @author liuzhiyong
*
*/
public class JdbcUtil {
private static String url = "jdbc:mysql://localhost:3306/mydb";
private static String user = "root";
private static String password = "root";
/**
* 静态代码块(只调用一次)
*/
static{
//注册驱动程序
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("驱动程序注册出错!");
}
}
/**
* 获取连接对象的方法
*/
public static Connection getConnection(){
try {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 释放资源的方法
*/
public static void close(Connection conn, Statement stmt){
//关闭资源(顺序:后打开,先关闭)
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
System.out.println("Statement关闭失败!");
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("Connection关闭失败!");
throw new RuntimeException(e);
}
}
}
}