Insert命令推送流程
package com.bjpowernode.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Insert命令推送流程 {
public static void main(String[] args) throws Exception{
//货物
String sql = "insert into dept values(60,'电力事业部','上海')";
String userName = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/bjpowernode";
//JDBC 步骤1:建立连接通道
Connection con = DriverManager.getConnection(url,userName,password);
//JDBC 步骤2:建立交通工具
PreparedStatement car = con.prepareStatement(sql);
//JDBC 步骤3:通信
int line = car.executeUpdate();
System.out.println("本次插入了"+line+"行数据");
//JDBC 步骤4:销毁资源
if(car != null) {
car.close();
}
if(con != null) {
con.close();
}
}
}
Delete命令推送流程
package com.bjpowernode.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Delete命令推送流程 {
public static void main(String[] args) throws Exception{
//货物
String sql = "DELETE FROM DEPT WHERE DEPTNO >= 50";
String userName = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/bjpowernode";
//JDBC 步骤1:建立连接通道
Connection con = DriverManager.getConnection(url,userName,password);
//JDBC 步骤2:建立交通工具
PreparedStatement car = con.prepareStatement(sql);
//JDBC 步骤3:通信
int line = car.executeUpdate();
System.out.println("本次删除了"+line+"行数据");
//JDBC 步骤4:销毁资源
if(car != null) {
car.close();
}
if(con != null) {
con.close();
}
}
}
Update命令推送流程
package com.bjpowernode.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Update命令推送流程 {
public static void main(String[] args) throws Exception{
//货物
String sql = "update dept set dname = '测试部门'";
String userName = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/bjpowernode";
//JDBC 步骤1:建立连接通道
Connection con = DriverManager.getConnection(url,userName,password);
//JDBC 步骤2:建立交通工具
PreparedStatement car = con.prepareStatement(sql);
//JDBC 步骤3:通信
int line = car.executeUpdate();
System.out.println("本次更新了"+line+"行数据");
//JDBC 步骤4:销毁资源
if(car != null) {
car.close();
}
if(con != null) {
con.close();
}
}
}
Select命令推送流程
executeUpdate与executeQuery
1.推送SQL命令类型不同
executeUpdate 专门推送DML命令(insert/update/delete)
executeQuery 专门推送DQL命令(select)
2.返回结果类型不同
executeUpdate 返回int类型结果,表示本次操作了多少个数据行
executeQuery 返回ResultSet接口类型结果,表示本次查询得到临时表
3.ResultSet对于临时表数据行读取管理
ResultSet对象通过指针管理数据行
ResultSet提供方法next():每次执行时要求指针向下移动一行。如果指针移动到的位置是一个数据行,则返回true,如果指针移动到位置不是一个数据行,则返回false
table.next()
4.遍历数据行
while(table.next() == true){
读取当前数据行指定字段中的内容
}
5.读取指针指向数据行中指定字段的值
int deptNo = table.getInt("deptNo");
String dname = table.getString("DNAME");
注:所有类型字段的值都可以通过getString方法读取。
package com.bjpowernode.test;
import java.sql.*;
public class Select命令推送流程 {
public static void main(String[] args) throws Exception{
String sql = "select * from dept";
String userName = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/bjpowernode";
//JDBC 步骤1:建立连接通道
Connection con = DriverManager.getConnection(url,userName,password);
//JDBC 步骤2:建立交通工具
PreparedStatement car = con.prepareStatement(sql);
//JDBC 步骤3:通信
ResultSet table = car.executeQuery();
//获得临时表字段个数和字段名称
ResultSetMetaData metaData = table.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <=columnCount ; i++) {
String columnName = metaData.getColumnName(i);
System.out.println("字段名称"+columnName);
}
//遍历临时表所有数据行信息
while (table.next()){
String deptNo = table.getString("deptNo");
String dname = table.getString("dname");
String loc = table.getString("loc");
System.out.println("部门编号 "+deptNo+" 部门名称 "+dname+" 部门位置 "+loc);
}
//JDBC 步骤4:销毁资源
if(table != null) {
table.close();
}
if(car != null) {
car.close();
}
if(con != null) {
con.close();
}
}
}
注:示范数据库的表的生成SQL命令如下:
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
CREATE TABLE DEPT(
DEPTNO int(2) not null,
DNAME VARCHAR(14),
LOC VARCHAR(13),
primary key (DEPTNO)
);
CREATE TABLE EMP(
EMPNO int(4) not null,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(4),
HIREDATE DATE DEFAULT NULL,
SAL DOUBLE(7,2),
COMM DOUBLE(7,2),
primary key (EMPNO),
DEPTNO INT(2)
);
CREATE TABLE SALGRADE(
GRADE INT,
LOSAL INT,
HISAL INT
);
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(40,'OPERATIONS','BOSTON');
commit;
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES(
7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
commit;
INSERT INTO SALGRADE(GRADE,LOSAL,HISAL)VALUES(1,700,1200);
INSERT INTO SALGRADE(GRADE,LOSAL,HISAL)VALUES(2,1201,1400);
INSERT INTO SALGRADE(GRADE,LOSAL,HISAL)VALUES(3,1401,2000);
INSERT INTO SALGRADE(GRADE,LOSAL,HISAL)VALUES(4,2001,3000);
INSERT INTO SALGRADE(GRADE,LOSAL,HISAL)VALUES(5,3001,9999);
commit;