JDBC实现DDL
DDL(Data Definition Language): 数据定义语言, 用于定义/修改/删除数据对象(如表)的数据结构。
@Test
public void testDDL() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/address";
String user = "root";
String password = "password";
Connection conn = null;
Statement stmt = null;
try {
// 1.驱动程序的注册
Class.forName(driver);
// 2.获取连接
conn = (Connection) DriverManager.getConnection(url,user,password);
// 3.创建statment
stmt = (Statement) conn.createStatement();
// 4.准备sql
String sql = "create table user_info( id int primary key not null AUTO_INCREMENT, username VARCHAR (20) , password VARCHAR (20) )AUTO_INCREMENT =1;";
// 5.执行sql语句,得到返回结果
int count = stmt.executeUpdate(sql);
// 6、获取返回结果
System.out.println("本次执行共影响了:" + count + "行数据");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
// 7.关闭连接资源(注意顺序:后打开的先关闭)
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
运行结果(成功生成表):
本次执行共影响了:0行数据
JDBC实现DML
DML(Data Manipulation Language): 数据操作语言,用于添加/修改/查询数据库中数据,操作数据库对象中包含的数据,也就是说操作的单位是记录。
insert
@Test
public void testInsert() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/address";
String user = "root";
String password = "password";
Connection conn = null;
Statement stmt = null;
try {
// 1.驱动程序的注册
Class.forName(driver);
// 2.获取连接
conn = (Connection) DriverManager.getConnection(url,user,password);
// 3.创建statment
stmt = (Statement) conn.createStatement();
// 4.准备sql
String sql = "INSERT INTO user_info (username, password) values ('user1','pwd1')";
// 5.执行sql语句,得到返回结果
int count = stmt.executeUpdate(sql);
// 6、获取返回结果
System.out.println("本次执行共影响了:" + count + "行数据");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
// 7.关闭连接资源(注意顺序:后打开的先关闭)
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
delete
@Test
public void testDelete() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/address";
String user = "root";
String password = "password";
Connection conn = null;
Statement stmt = null;
try {
// 1.驱动程序的注册
Class.forName(driver);
// 2.获取连接
conn = (Connection) DriverManager.getConnection(url,user,password);
// 3.创建statment
stmt = (Statement) conn.createStatement();
// 4.准备sql
String sql = " delete from user_info where id =1 ";
// 5.执行sql语句,得到返回结果
int count = stmt.executeUpdate(sql);
// 6、获取返回结果
System.out.println("本次执行共影响了:" + count + "行数据");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
// 7.关闭连接资源(注意顺序:后打开的先关闭)
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
update
@Test
public void testUpdate() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/address";
String user = "root";
String password = "password";
Connection conn = null;
Statement stmt = null;
try {
// 1.驱动程序的注册
Class.forName(driver);
// 2.获取连接
conn = (Connection) DriverManager.getConnection(url,user,password);
// 3.创建statment
stmt = (Statement) conn.createStatement();
// 4.准备sql
String sql = "UPDATE user_info SET username = 'iamupdate' where id =1 ";
// 5.执行sql语句,得到返回结果
int count = stmt.executeUpdate(sql);
// 6、获取返回结果
System.out.println("本次执行共影响了:" + count + "行数据");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
// 7.关闭连接资源(注意顺序:后打开的先关闭)
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
运行结果:
本次执行共影响了:1行数据
JDBC实现DQL
DQL:数据查询语言。
@Test
public void testQuery() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/address";
String user = "root";
String password = "password";
Connection conn = null;
Statement stmt = null;
try {
// 1.驱动程序的注册
Class.forName(driver);
// 2.获取连接
conn = (Connection) DriverManager.getConnection(url,user,password);
// 3.创建statment
stmt = (Statement) conn.createStatement();
String sql = " select * from user_info ";
ResultSet resultSet = stmt.executeQuery(sql);
//ResultSet 对象具有指向其当前数据行的光标。 最初,光标被置于第一行之前。调用 next() 方法将光标移动到下一行;
//因为该方法在 ResultSet 对象没有下一行时返回 false
//读取方法1 – 通过字段名称来读取
while(resultSet.next()){
int id = resultSet.getInt("ID"); //不区分大小写
String name = resultSet.getString("username");
String passwd = resultSet.getString("password");
System.out.println("id:"+id+" 用户名:"+name+" 密码:"+passwd);
}
System.out.println("-------------------------------------------------");
resultSet = stmt.executeQuery(sql);
//读取方法2 – 通过索引来遍历读取
while(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String passwd = resultSet.getString(3);
System.out.println("id:"+id+" 用户名:"+name+" 密码:"+passwd);
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
// 7.关闭连接资源(注意顺序:后打开的先关闭)
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
执行结果:
id:1 用户名:user1 密码:pwd1
id:2 用户名:user2 密码:pwd2
id:3 用户名:user3 密码:pwd3
-------------------------------------------------
id:1 用户名:user1 密码:pwd1
id:2 用户名:user2 密码:pwd2
id:3 用户名:user3 密码:pwd3
代码优化
以上代码重复率很高,看起来臃肿,简化一下,抽取出共同部分写个工具类,代码会看起来整洁易读一些,使用起来也会方便很多。另外,前文也说过,Statement不安全,相对起来,PrepareStatement比较安全,有预编译,执行量大的话更快。
SqlHelper.java
package myjdbc.demo;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class SqlHelper {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/address";
private static String user = "root";
private static String password = "password";
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
static{
//加载驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("jdbc驱动程序注册失败!"); //正式代码用log输出
}
}
/**
* 方法名:getConnection
* 详述:获取连接对象的方法
* 开发人员:NowUSeeMe
* @return Connection
*/
public static Connection getConnection() {
try {
return (Connection) DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 方法名:close
* 详述:释放资源
* 开发人员:NowUSeeMe
* @param rs
* @param conn
* @param stmt void
*/
public static void close(ResultSet rs, Connection conn, Statement stmt){
//关闭资源(先开后关)
if(null != rs)
{
try{
rs.close();
}
catch(SQLException e){
e.printStackTrace();
}
rs=null;
}
if(null != stmt){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(null != conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
/**
* 方法名:executeQuery
* 详述:查询
* 开发人员:NowUSeeMe
* @param sql
* @param parameters
* @return ResultSet
*/
public static ResultSet executeQuery(String sql,String[] parameters)
{
try
{
connection = getConnection();
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
if(null != parameters){
for(int i=0;i<parameters.length;i++)
{
preparedStatement.setString(i+1,parameters[i]);
}
}
resultSet = preparedStatement.executeQuery();
}catch(Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}finally{
}
return resultSet;
}
/**
* 方法名:executeUpdate
* 详述:实现增删改
* 开发人员:NowUSeeMe
* 创建时间:2017-2-11
* @param sql
* @param parameters void
*/
public static Integer executeUpdate(String sql,String[] parameters){
Integer temp = 0;
try {
connection = getConnection();
preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
if(null != parameters){
for(int i=0;i<parameters.length;i++)
{
preparedStatement.setString(i+1,parameters[i]);
}
}
temp = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
close(resultSet, connection, preparedStatement);
}
return temp;
}
}
测试查询代码:
@Test
public void testQuery() throws SQLException {
String sql = " select * from user_info ";
ResultSet resultSet = SqlHelper.executeQuery(sql, null);
while(resultSet.next()){
int id = resultSet.getInt("ID"); //不区分大小写
String name = resultSet.getString("username");
String passwd = resultSet.getString("password");
System.out.println("id:"+id+" 用户名:"+name+" 密码:"+passwd);
}
System.out.println("----------------------------------");
String sql2 = " select * from user_info where id = ? ";
String[] params = {"1"};
resultSet = SqlHelper.executeQuery(sql2, params);
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String passwd = resultSet.getString(3);
System.out.println("id:"+id+" 用户名:"+name+" 密码:"+passwd);
}
}
运行结果:
id:1 用户名:user1 密码:pwd1
id:3 用户名:user3 密码:pwd3
id:2 用户名:user2 密码:pwd2
----------------------------------
id:1 用户名:user1 密码:pwd1
测试删除代码:
@Test
public void testDelete() {
String sql = " delete from user_info where id =1 ";
Integer influentRows = SqlHelper.executeUpdate(sql, null);
System.out.println("InfluentRows: "+influentRows);
}
运行结果:
InfluentRows: 0