上季我们建立了与数据库的连接，本季我们将对数据库进行操作使用Statement 执行DDL、使用Statement 执行DML，常用的插入、更新、删除及查询数据哈~
• Statement 简介
–Statement 提供了一个操作数据库语句的功能，可通过它来创建表、插入记录、修改记录、删除记录等操作
• 获得Statement
–可以从数据库连接Connection中获得Statement
Connection conn = new ConnectionUtil().getConnection();
Statement stmt = conn.createStatement();
ConnectionUtil.java
package com.michael.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class ConnectionUtil {
//第一种方法
public Connection getConnection(){
Connection conn = null;
try {
//Class.forName加载驱动
Class.forName("com.mysql.jdbc.Driver");
//DriverManager获得连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin");
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//第二种方法
public Connection getConnection(String driver,String url,String user,String password){
Connection conn = null;
try {
//Class.forName加载驱动
Class.forName(driver);
//DriverManager获得连接
conn = DriverManager.getConnection(url,user,password);
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//第三种方法
public Connection openConnection(){
String driver = "";
String url = "";
String user = "";
String password = "";
Properties prop = new Properties();
Connection conn = null;
try {
//加载属性文件
prop.load(this.getClass().getClassLoader().getResourceAsStream("DBConfig.properties"));
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
password = prop.getProperty("password");
//Class.forName加载驱动
Class.forName(driver);
//DriverManager获得连接
conn = DriverManager.getConnection(url,user,password);
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
TestStatement.java
package com.michael.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestStatement {
public static void getStatement(){
Connection conn = new ConnectionUtil().openConnection();
try {
Statement stmt = conn.createStatement();
System.out.println(stmt);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Main.java
package com.michael.main;
import com.michael.jdbc.ConnectionUtil;
import com.michael.jdbc.TestStatement;
public class Main {
/**
* @param args
*/
public static void main(String[] args) {
ConnectionUtil cu = new ConnectionUtil();
//第一种方法
System.out.println("第一种方法:"+cu.getConnection());
//第二种方法
System.out.println("第二种方法:"+cu.getConnection("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin"));
//第三种方法
System.out.println("第三种方法:"+cu.openConnection());
TestStatement.getStatement();
}
}
测试结果：
• 使用Statement 执行DDL
–可以使用Statement来执行一个数据定义语句，例如：创建一张表
TestStatement.java
package com.michael.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestStatement {
public static void getStatement(){
Connection conn = new ConnectionUtil().openConnection();
try {
Statement stmt = conn.createStatement();
System.out.println(stmt);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void createTable(){
//DDL数据定义语句
Connection conn = new ConnectionUtil().openConnection();
String sql = "create table CustomerTbl(id int primary key auto_increment,name varchar(20),email varchar(20))";
try {
Statement stmt = conn.createStatement();
//执行SQL语句
stmt.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
conn = null;
e.printStackTrace();
}
}
}
}
Main.java
package com.michael.main;
import com.michael.jdbc.ConnectionUtil;
import com.michael.jdbc.TestStatement;
public class Main {
/**
* @param args
*/
public static void main(String[] args) {
ConnectionUtil cu = new ConnectionUtil();
//第一种方法
System.out.println("第一种方法:"+cu.getConnection());
//第二种方法
System.out.println("第二种方法:"+cu.getConnection("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin"));
//第三种方法
System.out.println("第三种方法:"+cu.openConnection());
TestStatement.getStatement();
TestStatement.createTable();
}
}
测试结果：
• 使用Statement 执行DML
–更新
• Insert
TestStatement.java
package com.michael.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class TestStatement {
public static void getStatement(){
Connection conn = new ConnectionUtil().openConnection();
try {
Statement stmt = conn.createStatement();
System.out.println(stmt);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void createTable(){
//DDL数据定义语句
Connection conn = new ConnectionUtil().openConnection();
String sql = "create table CustomerTbl(id int primary key auto_increment,name varchar(20),email varchar(20))";
try {
Statement stmt = conn.createStatement();
//执行SQL语句
stmt.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
conn = null;
e.printStackTrace();
}
}
}
//DML数据操作语句--CRUD:create、retrive、update、delete
public static void testInsert(){
//DDL数据定义语句
Connection conn = new ConnectionUtil().openConnection();
String sql = "insert into CustomerTbl(name,email) values('Michael','[email]michael@java.com[/email]')";
try {
Statement stmt = conn.createStatement();
//执行SQL语句
stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
conn = null;
e.printStackTrace();
}
}
}
}
Main.java
package com.michael.main;
import com.michael.jdbc.ConnectionUtil;
import com.michael.jdbc.TestStatement;
public class Main {
/**
* @param args
*/
public static void main(String[] args) {
ConnectionUtil cu = new ConnectionUtil();
//第一种方法
System.out.println("第一种方法:"+cu.getConnection());
//第二种方法
System.out.println("第二种方法:"+cu.getConnection("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin"));
//第三种方法
System.out.println("第三种方法:"+cu.openConnection());
TestStatement.getStatement();
//TestStatement.createTable();
TestStatement.testInsert();
}
}
测试结果：
#################Michael分割线#########################
#################Michael分割线#########################
本文转自redking51CTO博客，原文链接：http://blog.51cto.com/redking/151703，如需转载请自行联系原作者
