Statement概述
Statement 是 Java 执行数据库操作的一个重要接口,用于在已经建立数据库连接的基础上,向数据库发送要执行的SQL语句。Statement对象,用于执行不带参数的简单SQL语句。
Statement子接口有:CallableStatement, PreparedStatement。
Statement 接口提供了执行语句和获取结果的基本方法。PreparedStatement 接口添加了处理 IN 参数的方法;而 CallableStatement 添加了处理 OUT 参数的方法。
Statement 对象用于将 SQL 语句发送到数据库中。实际上有三种 Statement 对象,它们都作为在给定连接上执行 SQL 语句的包容器:Statement、PreparedStatement(它从 Statement 继承而来)和 CallableStatement(它从 PreparedStatement 继承而来)。它们都专用于发送特定类型的 SQL 语句: Statement 对象用于执行不带参数的简单 SQL 语句;PreparedStatement 对象用于执行带或不带 IN 参数的预编译 SQL 语句;CallableStatement 对象用于执行对数据库已存在的存储过程的调用。
Statement执行DDL语句
package com.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DDLStatement {
protected String url="jdbc:mysql://localhost:3306/user";
protected String user="root";
protected String password="123456";
protected String driverName="com.mysql.jdbc.Driver";
protected Connection conn=null;
protected Statement state=null;
public void create() {
try {
Class.forName(driverName);
conn=DriverManager.getConnection(url,user,password);
if(conn!=null) {
System.out.println("数据库连接成功!");
}
//创建statement对象
state=conn.createStatement();
//创建SQL语句
String sql="create table login(id int primary key not null auto_increment,username varchar(50),password varchar(50))";
//发送SQL语句并执行结果
int flag=state.executeUpdate(sql);
System.out.println(flag);
if(flag==0) {
System.out.println("表创建成功!");
}else {
System.out.println("表创建失败!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
程序运行结果:
数据库结果:
Statement执行DML语
3.1 数据库连接代码
package com.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCConection3 {
private String url="jdbc:mysql://localhost:3306/user";
private String user="root";
private String password="123456";
private Connection conn=null;
private Statement state=null;
public Connection getConn() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
return conn;
}
public void close(Statement state,Connection conn) {
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.2 增加数据
package com.JDBC;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class DMLInsert extends JDBCConection3{
protected Connection conn=null;
protected Statement state=null;
public void insert() throws ClassNotFoundException, SQLException {
//连接数据库
conn=super.getConn();
//编写SQL语句
String sql="insert into login (username,password) values('hehe','123456')";
//创建Statement对象
state=conn.createStatement();
int flag=state.executeUpdate(sql);
System.out.println(flag);
if(flag>0) {
System.out.println("数据插入成功");
}
super.close(state, conn);
}
}
程序运行结果:
数据库结果:
3.3 更新数据
package com.JDBC;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class DMLUpdate extends JDBCConection3 {
protected Connection conn=null;
protected Statement state=null;
public void update() throws ClassNotFoundException, SQLException {
//数据库连接
conn=super.getConn();
//编写SQL语句
String sql="update login set password='456789' where username='hehe'";
//创建Statement对象
state=conn.createStatement();
int flag=state.executeUpdate(sql);
System.out.println(flag);
if(flag>0) {
System.out.println("数据更新成功!");
}else {
System.out.println("数据更新失败!");
}
super.close(state, conn);
}
}
程序运行结果:
数据库结果:
3.4 删除数据
package com.JDBC;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class DMLDelete extends JDBCConection3{
protected Connection conn=null;
protected Statement state=null;
public void delete() throws ClassNotFoundException, SQLException {
//数据库连接
conn=super.getConn();
//编写SQL语句
String sql="delete from login where username='effort'";
//创建Statement对象
state=conn.createStatement();
int flag=state.executeUpdate(sql);
System.out.println(flag);
if(flag>0) {
System.out.println("数据删除成功!");
}else {
System.out.println("数据删除失败!");
}
super.close(state, conn);
}
}
程序运行结果:
数据库结果:
Statement执行DQL语句
4.1 查询数据
package com.JDBC;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DQLSelect extends JDBCConection3{
protected Connection conn=null;
protected Statement state=null;
protected ResultSet rs=null;
public void selectAll() throws ClassNotFoundException, SQLException {
//数据库连接
conn=super.getConn();
//编写SQL语句
String sql="select * from login";
//创建Statement对象
state=conn.createStatement();
//接收查询到的所有数据
ResultSet rs=state.executeQuery(sql);
while(rs.next()) {
System.out.println("用列名称取值: id:"+rs.getInt("id")+" username:"+rs.getString("username")+" password:"+rs.getString("password"));
System.out.println("用索引取值: id:"+rs.getInt(1)+" username:"+rs.getString(2)+" password:"+rs.getString(3));
}
super.close(rs, state, conn);
}
}
程序运行结果:
数据库结果: