这里使用的是阿里巴巴的工具类
1.Dao实现层
这里提供了两种,一种是手写DaoUtils完成Dao层的封装,一种是使用阿里巴巴的工具完成Dao层的封装
private QueryRunner runner=new QueryRunner(DruidDBUtils.getDataSource());
public int add(Student st) throws SQLException {
String sql="insert into student(name,age,email,bornDate,address) values(?,?,?,?,?)";
//1.手写DaoUtils完成数据的增删改查
//return DaoUtils.commonsUpdate(sql,st.getName(),st.getAge(),st.getEmail(),st.getBornDate(),st.getAddress());
//2.使用Druid工具完成数据的增删改查
return runner.update(sql,st.getName(),st.getAge(),st.getEmail(),st.getBornDate(),st.getAddress());
}
public int delById(int id) throws SQLException {
String sql="delete from student where id=?";
//return DaoUtils.commonsUpdate(sql,id);
return runner.update(sql,id);
}
public int updateById(int id, String name) throws SQLException {
String sql="update student set name=? where id=?";
//return DaoUtils.commonsUpdate(sql,name,id);
return runner.update(sql,id,name);
}
public List<Student> selelctAll() throws SQLException {
String sql="select * from student";
//List<Student> list=DaoUtils.commonsQuery(sql,Student.class);
//return list;
return runner.query(sql,new BeanListHandler<>(Student.class));
}
public Student selectById(int id) throws SQLException {
String sql="select * from student where id=?";
/* List<Student> list=DaoUtils.commonsQuery(sql,Student.class,id);
if (list.isEmpty()){//判断查询的id号所代表的信息是否存在
return null;
}
return list.get(0);//存在则取出*/
return runner.query(sql,new BeanHandler<>(Student.class),id);
}
2.Duid工具类
private static DataSource dataSource;
static {//静态代码块只加载一次
try {
Properties p=new Properties();//实例化map集合对象
p.load(new FileInputStream("user.properties"));
dataSource= DruidDataSourceFactory.createDataSource(p);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
//2.获取连接对象,并返回
Connection conn=null;
return conn=dataSource.getConnection();
}
public static DataSource getDataSource(){
return dataSource;
}
public static void closeAll(AutoCloseable...ac){
for (AutoCloseable c:ac){
if (c!=null){
try {
c.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
3.测试层
StudentDaoImpl studentDao=new StudentDaoImpl();
Scanner sc=new Scanner(System.in);
do {
System.out.println("请输入数字:1.添加 2.删除 3.修改 4.查询 5.查询单个 6.退出");
int n=sc.nextInt();
int res=0;
switch (n){
case 1:
int r=new Random().nextInt(100);//年龄设置取随机数
System.out.println("请输入要添加的姓名");
String name=sc.next();
System.out.println("请输入要添加的邮箱");
String email=sc.next();
System.out.println("请输入要添加的地址");
String address=sc.next();
Student st=new Student(r,name,r,email,new Date(),null);
res=studentDao.add(st);
System.out.println("添加:"+res);
break;
case 2:
System.out.println("请输入要删除的id");
int id=sc.nextInt();
res=studentDao.delById(id);
System.out.println("删除:"+res);
break;
case 3:
System.out.println("请输入要修改的id");
id=sc.nextInt();
res=studentDao.updateById(id,"HTML");
System.out.println("修改"+res);
break;
case 4:
List<Student> list=studentDao.selelctAll();
System.out.println(list);
break;
case 5:
System.out.println("请输入要查询的id");
id=sc.nextInt();
st=studentDao.selectById(id);
System.out.println(st);
break;
case 6:
System.out.println("结束");
return;
default:
System.out.println("输入错误!请重新输入");
break;
}
}while (true);