/**
* Project Name:cjxy
* File Name:AdminDaoImpl.java
* Package Name:edu.bjfu.cjxy.daoimpl
* Date:2013年9月3日 上午10:53:14
* Copyright (c) 2013, zhangzhaoyu0524@163.com All Rights Reserved.
*
*/
package edu.bjfu.cjxy.daoimpl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import edu.bjfu.cjxy.dao.AdminDao;
import edu.bjfu.cjxy.dao.BaseDao;
import edu.bjfu.cjxy.databean.SubAdminListRecord;
import edu.bjfu.cjxy.domain.Admin;
import edu.bjfu.cjxy.domain.SubstationAdmin;
import edu.bjfu.cjxy.utils.Page;
/**
* ClassName:AdminDaoImpl <br/>
* Function: 操作系统管理关和分站管理员. <br/>
* Reason: 操作系统管理员和分站管理员. <br/>
* Date: 2013年9月3日 上午10:53:14 <br/>
* @author zhangzhaoyu
* @version
* @since JDK 1.6
* @see
*/
@Repository
public class AdminDaoImpl implements BaseDao<Admin>, AdminDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public Admin getAdminByUserId(String userId) {
String sql = " SELECT * FROM superadmin WHERE userId = ? ";
return jdbcTemplate.queryForObject(sql, new Object[]{userId}, new RowMapper<Admin>() {
@Override
public Admin mapRow(ResultSet rs, int index) throws SQLException {
Admin admin = new Admin();
admin.setId(rs.getInt("id"));
admin.setUserId(rs.getString("userId"));
admin.setPassword(rs.getString("password"));
admin.setType(rs.getString("type"));
admin.setPhone(rs.getString("phone"));
admin.setEmail(rs.getString("email"));
return admin;
}
});
}
@Override
public SubstationAdmin getSubstationAdminByUserId(String userId) {
String sql = " SELECT * FROM admin WHERE userId = ? ";
return jdbcTemplate.queryForObject(sql, new Object[]{userId}, new RowMapper<SubstationAdmin>(){
@Override
public SubstationAdmin mapRow(ResultSet rs, int index) throws SQLException {
SubstationAdmin subAdmin = new SubstationAdmin();
subAdmin.setId(rs.getInt("id"));
subAdmin.setUserId(rs.getString("userId"));
subAdmin.setPassword(rs.getString("password"));
subAdmin.setType(rs.getString("type"));
subAdmin.setBranchId(rs.getInt("branchId"));
subAdmin.setLeixing(rs.getInt("leixing"));
subAdmin.setPhone(rs.getString("phone"));
subAdmin.setEmail(rs.getString("email"));
return subAdmin;
}});
}
@Override
public Page<Admin> getAdminByPage(Page<Admin> page) {
int countOfsuperadmin = getTotalCountOfAdmin();
//设置总的记录数
page.setTotalRecord(countOfsuperadmin);
String sql = "SELECT * FROM superadmin ORDER BY id LIMIT ?, ? ";
List<Admin> adminList = jdbcTemplate.query(
sql,
new Object[]{page.getOffset(),
page.getPageSize()},
new RowMapper<Admin>() {
@Override
public Admin mapRow(ResultSet rs, int index) throws SQLException {
Admin admin = new Admin();
admin.setId(rs.getInt("id"));
admin.setUserId(rs.getString("userId"));
admin.setPassword(rs.getString("password"));
admin.setType(rs.getString("type"));
admin.setPhone(rs.getString("phone"));
admin.setEmail(rs.getString("email"));
return admin;
}});
page.setDatas(adminList);
return page;
}
@Override
public int getTotalCountOfAdmin() {
return getTotalCount();
}
@Override
public Page<SubstationAdmin> getSubstationAdminByPage(Page<SubstationAdmin> page) {
int totalCountOfSubAdmin = getTotalCountOfSubstationAdmin();
//设置总记录数
page.setTotalRecord(totalCountOfSubAdmin);
String sql = "SELECT * FROM admin ORDER BY id LIMIT ?, ? ";
final List<SubstationAdmin> subAdminList = new ArrayList<SubstationAdmin>();
jdbcTemplate.query(sql, new Object[]{page.getOffset(), page.getPageSize()},
new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
SubstationAdmin subAdmin = new SubstationAdmin();
subAdmin.setId(rs.getInt("id"));
subAdmin.setUserId(rs.getString("userId"));
subAdmin.setPassword(rs.getString("password"));
subAdmin.setBranchId(rs.getInt("branchId"));
subAdmin.setType(rs.getString("type"));
subAdmin.setBranchId(rs.getInt("branchId"));
subAdmin.setLeixing(rs.getInt("leixing"));
subAdmin.setPhone(rs.getString("phone"));
subAdmin.setEmail(rs.getString("email"));
subAdminList.add(subAdmin);
}
});
page.setDatas(subAdminList);
return page;
}
@Override
public int getTotalCountOfSubstationAdmin() {
String sql = " SELECT count(*) FROM admin ";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
@Override
public boolean insert(Admin obj) {
String sql = " INSERT INTO superadmin(userId, password, type, phone, email) VALUES(?, ?, ?, ?, ?)";
Object[] args = new Object[]{
obj.getUserId(),
obj.getPassword(),
obj.getType(),
obj.getPhone(),
obj.getEmail()
};
int[] argTypes = new int[] {
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR
};
return jdbcTemplate.update(sql, args, argTypes) > 0;
}
@Override
public boolean delete(Admin obj) {
String sql = null;
Object[] args = null;
if (obj.getId() != null) {
sql = " DELETE FROM superadmin WHERE id = ? ";
args = new Object[]{obj.getId()};
} else if(obj.getUserId() != null) {
sql = " DELETE FROM superadmin WHERE userId = ? ";
args = new Object[]{obj.getUserId()};
} else {
return false;
}
return jdbcTemplate.update(sql, args) > 0;
}
@Override
public boolean update(Admin obj) {
String sql = " UPDATE superadmin SET userId = ?, password = ?, type = ?, phone = ?, email = ? WHERE id = ? ";
Object[] args = new Object[]{
obj.getUserId(),
obj.getPassword(),
obj.getType(),
obj.getPhone(),
obj.getEmail(),
obj.getId()
};
int[] argTypes = new int[]{
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.VARCHAR,
java.sql.Types.INTEGER
};
return jdbcTemplate.update(sql, args, argTypes) > 0;
}
@Override
public Admin queryForObject(int id) {
String sql = " SELECT * FROM superadmin WHERE id = ? ";
return jdbcTemplate.queryForObject(sql, new Object[]{id},
BeanPropertyRowMapper.newInstance(Admin.class));
}
@Override
public int getTotalCount() {
String sql = " SELECT count(*) FROM superadmin ";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
@Override
public Page<Admin> queryForDataByPage(Page<Admin> page, Admin obj) {
if (obj != null) {
String sql = " select count(*) from superadmin where userId like '%" + obj.getUserId() + "%'";
int totalCount = jdbcTemplate.queryForObject(sql, Integer.class);
page.setTotalRecord(totalCount);
sql = "SELECT * FROM superadmin where userId like '%?%' LIMIT ?, ? ";
Object[] args = new Object[]{
obj.getUserId(),
page.getOffset(),
page.getPageSize()
};
List<Admin> adminList = jdbcTemplate.query(sql, args, new RowMapper<Admin>() {
@Override
public Admin mapRow(ResultSet rs, int index) throws SQLException {
Admin admin = new Admin(rs.getInt("id"), rs.getString("userId"),
rs.getString("password"), rs.getString("type"));
admin.setPhone(rs.getString("phone"));
admin.setEmail(rs.getString("email"));
return admin;
}
});
page.setDatas(adminList);
return page;
}
return null;
}
@Override
public void executeSql(String sql) {
jdbcTemplate.execute(sql);
}
@Override
public boolean modifyAdminPassword(Admin admin) {
String sql = " UPDATE superadmin SET password = ? WHERE id = ? ";
return jdbcTemplate.update(sql, new Object[]{admin.getPassword(), admin.getId()},
new int[]{java.sql.Types.VARCHAR,java.sql.Types.INTEGER}) > 0;
}
@Override
public boolean modifySubAdminPassword(SubstationAdmin subadmin) {
String sql = " UPDATE admin SET password = ? WHERE id = ? ";
return jdbcTemplate.update(sql, new Object[]{subadmin.getPassword(), subadmin.getId()},
new int[]{java.sql.Types.VARCHAR,java.sql.Types.INTEGER}) > 0;
}
@Override
public Page<SubAdminListRecord> getSubstationAdminListRecordByPage(
Page<SubAdminListRecord> page, SubAdminListRecord sub) {
if (sub == null) {
int totalCount = getTotalCountOfSubstationAdmin();
page.setTotalRecord(totalCount);
String sql = " SELECT admin.id, admin.userId, admin.password, admin.branchId, admin.leixing, "
+ " admin.phone, admin.email, branchschool.name FROM admin, branchschool where "
+ " admin.branchId = branchschool.id ORDER BY id LIMIT ?, ? ";
List<SubAdminListRecord> datas = jdbcTemplate.query(sql, new Object[]{page.getOffset(), page.getPageSize()},new int[]{java.sql.Types.INTEGER, java.sql.Types.INTEGER},
new RowMapper<SubAdminListRecord>() {
@Override
public SubAdminListRecord mapRow(ResultSet rs, int rowNum) throws SQLException {
SubAdminListRecord data = new SubAdminListRecord();
data.setId(rs.getInt("id"));
data.setUserId(rs.getString("userId"));
data.setPassword(rs.getString("password"));
data.setLeixing(rs.getInt("leixing"));
data.setPhone(rs.getString("phone"));
data.setEmail(rs.getString("email"));
data.setBranchSchoolName(rs.getString("name"));
return data;
}
});
page.setDatas(datas);
return page;
}
return null;
}
@Override
public SubstationAdmin getSubstationAdminById(int subAdminId) {
String sql = " SELECT * FROM admin WHERE id = ? ";
return jdbcTemplate.queryForObject(sql, new Object[]{subAdminId}, new RowMapper<SubstationAdmin>(){
@Override
public SubstationAdmin mapRow(ResultSet rs, int index) throws SQLException {
SubstationAdmin subAdmin = new SubstationAdmin();
subAdmin.setId(rs.getInt("id"));
subAdmin.setUserId(rs.getString("userId"));
subAdmin.setPassword(rs.getString("password"));
subAdmin.setType(rs.getString("type"));
subAdmin.setBranchId(rs.getInt("branchId"));
subAdmin.setLeixing(rs.getInt("leixing"));
subAdmin.setPhone(rs.getString("phone"));
subAdmin.setEmail(rs.getString("email"));
return subAdmin;
}});
}
@Override
public boolean insertSubAdmin(SubstationAdmin subAdmin) {
String sql = " INSERT INTO admin(userId, password, type, branchId, leixing, phone, email) VALUES(?, ?, ?, ?, ?, ?, ?)";
Object[] args = new Object[]{
subAdmin.getUserId(),
subAdmin.getPassword(),
subAdmin.getType(),
subAdmin.getBranchId(),
subAdmin.getLeixing(),
subAdmin.getPhone(),
subAdmin.getEmail()
};
return jdbcTemplate.update(sql, args) > 0;
}
@Override
public boolean deleteSubAdmin(SubstationAdmin subAdmin) {
String sql = "DELETE FROM admin WHERE id = ? ";
return jdbcTemplate.update(sql, new Object[]{subAdmin.getId()},
new int[]{java.sql.Types.INTEGER}) > 0;
}
@Override
public boolean updateSubAdmin(SubstationAdmin subAdmin) {
String sql = "UPDATE admin SET userId = ?, password = ?, type = ?, branchId = ?, "
+ " leixing = ?, phone = ?, email = ? WHERE id = ? ";
Object[] args = new Object[]{
subAdmin.getUserId(),
subAdmin.getPassword(),
subAdmin.getType(),
subAdmin.getBranchId(),
subAdmin.getLeixing(),
subAdmin.getPhone(),
subAdmin.getEmail(),
subAdmin.getId()
};
return jdbcTemplate.update(sql, args) > 0;
}
@Override
public int getNameNum(String userId) {
String sql = " SELECT count(*) FROM superadmin WHERE userId = '"+userId+"'";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
@Override
public int getSubNameNum(String userId) {
String sql = " SELECT count(*) FROM admin WHERE userId = '"+userId+"'";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
}
分享到:
相关推荐
Spring JdbcTemplate调用Oracle存储过程输出游标结果集实现增删改查
完成功能:能够对用户进行CRUD操作,界面粗糙,只做演示 运行环境:eclipse2019.03+JDK8+Tomcat9.0.41+MySQL5.5 运用到的技术:spring+springMVC+jdbctemplate+MVC框架 具体细节: ①查询所有员工列表:jdbcTemplate...
Spring MVC应用使用JDBC操作使用JDBCTemplate进行CRUD操作
spring中使用JdbcTemplate操作数据库crud,一图详解(脑图)
本文主要介绍了使用spring的jdbctemplate进行增删改查的基类Dao的简单写法,需要的朋友可以参考下
使用JDBC模板的Spring MVC CRUD这是一个简单的SPRING MVC项目,其中我使用了Spring MVC,JDBC模板进行数据操作,而在数据库方面,我使用了MYSQL数据库。脚步- 1.创建名称为“ usersdb”的MYSQL数据库2.使用以下命令...
spring-jdbctemplate-example 这是一个Spring Monolith应用程序示例,该示例使用jdbcTemplate连接MySQL数据库并执行反馈操作的操作。测试和构建运行测试(也运行大型测试) ./gradlew clean test 有条件地根据测试...
使用JdbcTemplate完成CRUD操作的完整代码弹簧需要执行哪些操作从GitHub上导入Eclipse进行Maven安装(Git Pull)#Create DB 删除数据库manishjdbc; 创建数据库manishjdbc; 使用manishjdbc; 创建表customer...
java实现别踩白块儿源码从头开始具有JdbcTemplate和MariaDB的Spring5 MVC的CRUD示例 有时我们会浪费时间从头开始创建一个新项目。 只需克隆此项目。 在本地计算机上运行。 微笑和快乐的编码。 项目要求 Java 9 ...
一个基于 struts1.2+spring+tomcat数据源的论坛项目。...数据的CRUD采用spring的JDBCTemplate 里面做了权限控制 用到了tomcat数据源(比较老的技术) 部署非常简单,里面有详细说明文档 毕设的好材料
3、实现一套简单的ORM(直接使用spring rowmapper,insert自己实现),可以基于对象进行crud和相对复杂(感觉比hibernate强大一点)的sql操作; 4、基于对象指定查询的字段,大部分时候可以忘掉表结构进行业务...
1、CRUD操作 2、两种Dao开发方式 3、SqlMapConfig.xml编写及理解 4、mappere.xml编写及理解 5、动态sql应用 6、多表关联查询 7、延长加载策略 8、xml和注解开发 Spring基础 第一阶段 开源框架源码剖析 1、使用spring...
* 包括jsp,servlet,html,javascript,css等,以及springmvc+jdbcTemplate和 struts2+hibernate+spring, struts2+ibatis+spring 实现的登陆,CRUD,jfreechart图表显示等例子,用来演示两个mvc框架的基本使用,...
springboot-父子工程-初始版(包含CRUD示例接口) jpa-hibernate-dm: 数据库为{国产达梦数据库},持久层技术为jpa-hibernate mybatis-dm: 数据库为{国产达梦数据库},持久层技术为mybatis mybatis-plus-dm: 数据库为{...
Spring的Ioc Spring的AOP , AspectJ Spring的事务管理 , 三大框架的整合 目录 1.1 Spring 框架学习路线:..........................................................................................................
希望在自己的机器模拟一下公司中微服务的构建,使用docker部署了mysql实例,使用spring boot进行了CRUD(增删改查)操作进行了一下验证,在后面的学习中也可以尝试更多的框架和组件。 ps:实验环境是:ubuntu 14.04, ...