JDBC五步走操作
1.加载驱动
2.建立连接
3.预处理执行sql
4.执行executeUpdate和executeQuery（）
5.关闭资源
JDBCUTIL（里面封装了两个主要函数）
package Homework;
import com.lss.entity.emp;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
public class JDBCUTILs {
private static String user;
private static String password;
private static String url;
//静态方法 提前调用
static {
InputStream stream=null;
Properties properties=null;
try {
stream=JDBCUTILs.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(stream);
user=properties.getProperty(user);
password=properties.getProperty(password);
url=properties.getProperty(url);
stream.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
// get connection
public static Connection getConnection() throws Exception{
Connection con=null;
try {
con=DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
// 因为这里增删改查中增删改都返回的是改变的序列号，所以可以视为一个函数来实现sql语言的操作
// 但是查询要另外修改因为里面返回的是集合
public static int exeuteUpdate(String sql,Object[] params) throws Exception{
// 这里是增删改的操作，首先建立连接，statement接口来传递曹祖数据库，并返回更新的序列号
Connection con=null;
con=JDBCUTILs.getConnection();
PreparedStatement statement=null;
try {
statement= con.prepareStatement(sql);
for (int i=0;i< params.length;i++){
statement.setObject(i+1,params[i]);
// 这里的第一个数据是从1开始的，从params里面取到一个数据项的信息
}
int i=statement.executeUpdate();//这里返回的是整型
return i;
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally {
closeAll(null,statement);
}
}
return 0;
}
// query (it returns the collection,so we give it method respectively)
//这里返回的是集合，即是得到查询的信息
public static List<Map<String,Object>> exeuteQuery (String sql, Object[] params) throws Exception {
Connection con=null;
con=JDBCUTILs.getConnection();
PreparedStatement statement=null;
try {
statement= con.prepareStatement(sql);
for (int i=0;i< params.length;i++){
statement.setObject(i+1,params[i]);
}
ResultSet resultSet=statement.executeQuery();
List<Map<String,Object>>data=new ArrayList<>();
// int i=statement.executeUpdate(); there should be Set or Map to collect more data which return from "select"
int columnCount=resultSet.getMetaData().getColumnCount();
// Retrieves the number, types and properties of this ResultSet object's columns.
while (resultSet.next()){
Map<String,Object> map=new HashMap<>();
for (int i=0;i<columnCount;i++){
map.put(resultSet.getMetaData().getColumnLabel(i),resultSet.getObject(i));
}
// 这里key是取得的列数，value：Object忽略了各种数据类型，java为强数据类型
data.add(map);
}
return data;
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
finally {
closeAll(null,statement);
}
}
return null;
}
//判断是否存在这个数据项
public static boolean isExist(int id) throws Exception {
PreparedStatement statement = null;
emp emp = null;
ResultSet rs = null;
try {
String sql = "select *from user where id=?" + id;
rs = (ResultSet) JDBCUTILs.exeuteQuery(sql, null);
List<emp>list1=new ArrayList<>();
if (rs.next()) {
int Id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
int salary = rs.getInt("salary");
String job = rs.getString("job");
String dept = rs.getString("dept");
emp = new emp(Id, name, age, salary, job, dept);
list1.add(emp);
}
System.out.println(list1);
}
finally{
closeAll(null,statement);
}
if(emp==null)
return true;
else
return false;
}
//关闭资源
public static void closeAll(Connection con,PreparedStatement statement1,ResultSet set1) {
if (con!=null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement1!=null) {
try {
statement1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (set1!=null) {
try {
set1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//不返回结果集，返回序列集的关闭
public static void closeAll(Connection con,PreparedStatement statement1) {
if (con!=null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement1!=null) {
try {
statement1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Services；封装添加增删改查四函数，调用两个执行函数
package Homework;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import Homework.*;
import com.lss.entity.emp;
public class Services {
//add delete update (all return the number,so we use it in one method)
//增加方法，取得数据后先判断是否已经存在该数据，如果存在，则不添加
public static void add(emp emp) throws Exception {
String sql = "insert into emp（id,name,age,salary,entryTime,job,dept） values(?,?,?,?,?,?,?)";
int id=emp.getId();
Object[] params = {emp.getId(), emp.getName(), emp.getAge(), emp.getSalary(), emp.getJob(), emp.getEntryTime(),emp.getDept()};
if (!JDBCUTILs.isExist(id)) {
JDBCUTILs.exeuteUpdate(sql, params);
System.out.println("添加成功");
}
else System.out.println("数据已经存在");
}
//输入id后再进行判断
public static void delete(int id) throws Exception {
String sql = "delete from emp where id=?";
Object[] params = {id};
if (JDBCUTILs.isExist(id)) {
JDBCUTILs.exeuteUpdate(sql, params);
System.out.println("删除成功");
}
else System.out.println("数据不存在");
}
//同add方法，输入后进行判断
// just as the method of add,putting before judging
public static void update(int id,emp emp) throws Exception {
String sql = "update user set name=?,age=?,salary=?,job=?,dept=?,where id=?";
Object[] params = {emp.getId(), emp.getName(), emp.getAge(), emp.getSalary(), emp.getJob(), emp.getDept()};
if (JDBCUTILs.isExist(id)) {
JDBCUTILs.exeuteUpdate(sql, params);
System.out.println("更新成功");
}
else System.out.println("数据不存在");
}
public static void query(int id) throws Exception {
PreparedStatement statement = null;
emp emp = null;
ResultSet rs = null;
if (JDBCUTILs.isExist(id)) {
String sql = "select *from user where id=?" + id;
rs = (ResultSet) JDBCUTILs.exeuteQuery(sql, null);
if (rs.next()) {
int Id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
int salary = rs.getInt("salary");
String job = rs.getString("job");
String dept = rs.getString("dept");
emp = new emp(Id, name, age, salary, job, dept);
System.out.println("查询成功");
}
else
System.out.println("数据不存在");
}
}
}
测试Demo
package Homework;
import com.lss.*;
import com.lss.entity.emp;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
public class Demo {
public static void main(String[] args) throws Exception {
Services services=null;
Date entryTime=new Date();
Scanner scan = new Scanner(System.in);
System.out.println("请输入数字；1（增加），2（删除），3（修改），4（查询）");
int i=scan.nextInt();
switch (i){
case 1:
System.out.println("即将进行增加操作");
services.add(new emp(14,"mike",34,4900,"工程师",entryTime,"研发部"));
case 2:{
System.out.println("即将进行删除操作");
services.delete(14);
}
case 3:{
System.out.println("即将进行增加操作");
services.update(15,new emp(15,"mike",34,4900,"工程师",entryTime,"研发部"));
}
case 4:{
System.out.println("即将进行增加操作");
services.query(14);
}
}
}
}
也许后面修改，学习中ing...
1817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
