package com.bjsxt;
import java.nio.file.attribute.UserPrincipalLookupService;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* PreparedStatement对象的使用
* @author Administrator
*
*/
public class PreparedStatementDemo {
//向Departments表中插入一条数据
public void insertDempartments(String departmentName,int locationId){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("insert into departments values(default,?,?)");
ps.setString(1, departmentName);
ps.setInt(2, locationId);
ps.execute();
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.closeResource(ps, conn, null);
}
}
//更新数据
public void updateDepartment(int departmentId,String departmentName,int localhostId){
Connection conn= null;
PreparedStatement ps = null;
try{
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("update departments set department_name = ?,location_id = ? where department_id = ?");
ps.setString(1, departmentName);
ps.setInt(2, localhostId);
ps.setInt(3, departmentId);
ps.execute();
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.closeResource(ps, conn, null);
}
}
//完成数据查询
public Departments selectDepartmentsById(int departmentId){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Departments dept = null;
try{
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("select * from departments where department_id = ?");
ps.setInt(1, departmentId);
rs = ps.executeQuery();
while(rs.next()){
//ORM: Object Relational Mapping
dept=new Departments();
dept.setDepartmentId(rs.getInt("department_id"));
dept.setDepartmentName(rs.getString("department_name"));
dept.setLocationId(rs.getInt("location_id"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.closeResource(ps, conn, rs);
}
return dept;
}
//查询部门表中的部门名称，找到那些包含“人力”的部门信息
public List<Departments> selectDepartmentByLikeName(String departmentName){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Departments> list = new ArrayList<>();
try{
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("select * from departments where department_name like ?");
ps.setString(1, "%"+departmentName+"%");
rs = ps.executeQuery();
while(rs.next()){
Departments dept = new Departments();
dept.setDepartmentId(rs.getInt("department_id"));
dept.setDepartmentName(rs.getString("department_name"));
dept.setLocationId(rs.getInt("location_id"));
list.add(dept);
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.closeResource(ps, conn, rs);
}
return list;
}
//批量添加
public void addBatch(List<Departments> list){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtil.getConnection();
ps = conn.prepareStatement("insert into departments values(default,?,?)");
for(int i=0;i<list.size();i++){
ps.setString(1, list.get(i).getDepartmentName());
ps.setInt(2, list.get(i).getLocationId());
//添加批处理
ps.addBatch();
}
int[] arr =ps.executeBatch();
for(int i=0;i<arr.length;i++){
System.out.println(i);
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.closeResource(ps, conn, null);
}
}
//事务处理
public void deleteDempartments(String depratmentName){
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtil.getConnection();
//关闭事务的自动提交
conn.setAutoCommit(false);
ps = conn.prepareStatement("delete from departments where department_name like ?");
ps.setString(1, "%"+depratmentName+"%");
ps.executeUpdate();
ps = conn.prepareStatement("insert into departments values(default,'开发部',2)");
ps.executeUpdate();
String str = null;
str.length();
conn.commit();
}catch(Exception e){
e.printStackTrace();
JdbcUtil.rollback(conn);
}finally{
JdbcUtil.closeResource(ps, conn, null);
}
}
public static void main(String[] args) {
PreparedStatementDemo demo = new PreparedStatementDemo();
//demo.insertDempartments("人力资源部3", 10);
//demo.updateDepartment(10, "人力资源部10", 20);
/*Departments dept = demo.selectDepartmentsById(10);
if(dept != null){
System.out.println(dept.getDepartmentId()+" "+dept.getDepartmentName()+" "+dept.getLocationId());
}*/
/*List<Departments> list = demo.selectDepartmentByLikeName("人力");
for(Departments dept :list){
System.out.println(dept.getDepartmentId()+" "+dept.getDepartmentName()+" "+dept.getLocationId());
}*/
/*
List<Departments> list = new ArrayList<>();
for(int i=1;i<=10;i++){
Departments dept =new Departments();
dept.setDepartmentName("教学部"+i);
dept.setLocationId(20+i);
list.add(dept);
}
demo.addBatch(list);*/
demo.deleteDempartments("研发部");
}
}
报错代码：
java.lang.NullPointerException
at com.bjsxt.PreparedStatementDemo.deleteDempartments(PreparedStatementDemo.java:138)
at com.bjsxt.PreparedStatementDemo.main(PreparedStatementDemo.java:169)老师，问一下，这个报错的原因是什么呢？
老师，这些列代表啥意思啊，为啥我查的summary方法的解读没有这些列
京公网安备 11011402011233号