java数据库后台管理应该分层管理,不要在前端页面直接SQL数据.前端页面应该都是java对象数据.
一、我们看到的存放数据的表,实际就是为了存放数据的实体类,我们一般把它放到model层。
1、创建实体:DeadBreed 类
package com.xlkj.model;
import java.io.Serializable;
/**
- 死亡种猪登记
/
import java.util.Date;
public class DeadBreed implements Serializable{
/*
*
*/
private static final long serialVersionUID = -9137310603203490816L;
private int FID;//内码ID
private String FBillNo;//单据号
private Date FDate;//死亡时间
private int FBreedID;//种猪ID
private int FType;//种类
private String FBreedNumber;//耳号
private String FBreedName;//品种
private int FFenceID;//栏号ID
private String FFenceName;//栏号名称
private int FPersonid;//处理人
private String FGive;//死亡原因
private String FNote;//备注
private String FZren;//责任确认
private String FCreatorID;//创建人
private Date FCreateTime;//建立时间
private int FVoucherID;//凭证号
public DeadBreed(){
this.FVoucherID=0;
}
public String getFBillNo() {
return FBillNo;
}
public void setFBillNo(String fBillNo) {
FBillNo = fBillNo;
}
public String getFBreedName() {
return FBreedName;
}
public void setFBreedName(String fBreedName) {
FBreedName = fBreedName;
}
public String getFZren() {
return FZren;
}
public void setFZren(String fZren) {
FZren = fZren;
}
public int getFType() {
return FType;
}
public void setFType(int fType) {
FType = fType;
}
public int getFID() {
return FID;
}
public void setFID(int fID) {
FID = fID;
}
public Date getFDate() {
return FDate;
}
public void setFDate(Date fDate) {
FDate = fDate;
}
public int getFBreedID() {
return FBreedID;
}
public void setFBreedID(int fBreedID) {
FBreedID = fBreedID;
}
public String getFBreedNumber() {
return FBreedNumber;
}
public void setFBreedNumber(String fBreedNumber) {
FBreedNumber = fBreedNumber;
}
public int getFFenceID() {
return FFenceID;
}
public void setFFenceID(int fFenceID) {
FFenceID = fFenceID;
}
public String getFFenceName() {
return FFenceName;
}
public void setFFenceName(String fFenceName) {
FFenceName = fFenceName;
}
public int getFPersonid() {
return FPersonid;
}
public void setFPersonid(int fPersonid) {
FPersonid = fPersonid;
}
public String getFGive() {
return FGive;
}
public void setFGive(String fGive) {
FGive = fGive;
}
public String getFNote() {
return FNote;
}
public void setFNote(String fNote) {
FNote = fNote;
}
public String getFCreatorID() {
return FCreatorID;
}
public void setFCreatorID(String fCreatorID) {
FCreatorID = fCreatorID;
}
public Date getFCreateTime() {
return FCreateTime;
}
public void setFCreateTime(Date fCreateTime) {
FCreateTime = fCreateTime;
}
public int getFVoucherID() {
return FVoucherID;
}
public void setFVoucherID(int fVoucherID) {
FVoucherID = fVoucherID;
}
}
2、实体类对应到数据表:DeadBreed.hbm.xml
<class name="DeadBreed" table="t_DeadBreed">
<id name="FID">
<generator class="identity" />
</id>
<property name="FBillNo"/>
<property name="FDate" />
<property name="FType"/>
<property name="FBreedID" />
<property name="FBreedNumber" />
<property name="FBreedName"/>
<property name="FFenceID" />
<property name="FFenceName" />
<property name="FPersonid"/>
<property name="FGive"/>
<property name="FZren"/>
<property name="FNote"/>
<property name="FCreatorID"/>
<property name="FCreateTime"/>
<property name="FVoucherID"/>
</class>
二、建立基本的数据库接口,就是对数据增加,删除,修改,查询等的接口方法:BaseDao (interface)
package com.xlkj.dao;
import java.util.List;
/**
@author 余中伦 E-mail:371891632@qq.com
@version 创建时间:2022-9-3 下午5:12:01
类说明:
*/
public interface BaseDao {
//@保存,更新,删除数据
public void save(String sql,Object[] args);
public void update(String sql,Object[] args);
public void delete(String sql,Object[] args);
//@查询数据,判断数据
public List<?> getListDate(String sql,String className,Object[] args);
public Object getObject(String sql,String className,Object[] args);
public int getCounts(String sql,Object[] args);
}
三、实现上面接口BaseDao程序类:BaseDaoImpl
package com.xlkj.dao.impl;
import java.util.List;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import com.xlkj.dao.BaseDao;
import com.xlkj.util.JdbcProUtils;
/**
@author 余中伦 E-mail:371891632@qq.com
@version 创建时间:2022-9-3 下午5:13:40
类说明:JdbcProUtils类是采用的连接池
*/
public class BaseDaoImpl implements BaseDao {
private SimpleJdbcTemplate jdbc = new SimpleJdbcTemplate(
JdbcProUtils.getDataSource());
@Override
public void save(String sql,Object[] args) {
this.jdbc.update(sql,args);
}
@Override
public void update(String sql,Object[] args) {
this.jdbc.update(sql, args);
}
@Override
public void delete(String sql, Object[] args) {
this.jdbc.update(sql, args);
}
public List<?> getListDate(String sql, String className,Object[] args) {
@SuppressWarnings("rawtypes")
Class c=null;
Object o=null;
try {
c = Class.forName(className);
try {
o=c.newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return this.jdbc.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(o.getClass()),args);
}
@Override
public Object getObject(String sql, String className, Object[] args) {
@SuppressWarnings("rawtypes")
Class c=null;
Object o=null;
try {
c = Class.forName(className);
try {
o=c.newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return this.jdbc.queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(o.getClass()),args);
}
@Override
public int getCounts(String sql, Object[] args) {
return this.jdbc.queryForInt(sql, args);
}
}
四、基础的数据库服务类:BaseService,就是把服务层单独为一层。为我们后面的实体类的服务,是数据库打交道的一层。
package com.xlkj.service;
import java.util.List;
import com.xlkj.dao.BaseDao;
import com.xlkj.dao.impl.BaseDaoImpl;
/**
@author 余中伦 E-mail:371891632@qq.com
@version 创建时间:2022-9-3 下午5:24:10
类说明:
*/
public class BaseService {
private BaseDao baDao=new BaseDaoImpl();
public BaseDao getBaDao() {
return baDao;
}
public void setBaDao(BaseDao baDao) {
this.baDao = baDao;
}
public void save(String sql,Object[] args) {
this.baDao.save(sql, args);
}
public void update(String sql,Object[] args) {
this.baDao.update(sql, args);
}
public void delete(String sql, Object[] args) {
this.baDao.delete(sql, args);
}
public List<?> getListDate(String sql, String className,Object[] args) {
return this.baDao.getListDate(sql, className,args);
}
public Object getObject(String sql, String className, Object[] args) {
return this.baDao.getObject(sql, className, args);
}
public int getCounts(String sql, Object[] args) {
return this.baDao.getCounts(sql, args);
}
}
五、数据库连接池的实现 JdbcProUtils ,注意该类中dbcpconfig.properties配置文件哦。该配文件放到源码的根目录就是工程的 source下。该配文件就是写数据驱动,连接IP,用户及密码等。
package com.xlkj.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class JdbcProUtils {
private static DataSource myDataSource = null;
private JdbcProUtils() {
}
static {
try {
Properties prop = new Properties();
InputStream dbc = JdbcProUtils.class.getClassLoader()
.getResourceAsStream(“dbcpconfig.properties”);
prop.load(dbc);
myDataSource = BasicDataSourceFactory.createDataSource(prop);
}catch(SQLException E){
E.printStackTrace();
}
catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static DataSource getDataSource() {
return myDataSource;
}
public static Connection getConnection() throws SQLException {
return myDataSource.getConnection();
}
public static Statement getStmt(Connection conn) throws SQLException{
return myDataSource.getConnection().createStatement();
}
public static void closeStmt(Statement stmt){
try{
if(stmt!=null){
stmt.close();
stmt=null;
}
}catch(SQLException e){
e.printStackTrace();
}
}
public static void release(Object o)
{
try{
if(o instanceof ResultSet){
((ResultSet)o).close();
}else if(o instanceof Statement){
((Statement)o).close();
}else if(o instanceof Connection){
((Connection)o).close();
}
}catch(Exception e){
e.printStackTrace();
}
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
六、具体类的服务方法,就是保存数据,删除数据,修改数据,查询数据等,我举例说明:我创建了一个DeadBreedService继存于BaseService类,并重写该类的方法,并且添加自己想的方法,但自己的方法都去调用BaseService类中的方法:
package com.xlkj.service;
import java.util.Date;
import java.util.List;
import com.xlkj.model.DeadBreed;
/**
-
@author 余中伦 E-mail:371891632@qq.com
-
@version 创建时间:2022-9-3 下午5:26:30 类说明:
*/
public class DeadBreedService extends BaseService {private String className = “com.xlkj.model.DeadBreed”;
public void saveDeadBreed(DeadBreed db) {
String sql = “insert into t_DeadBreed(FBillNo,FDate,FBreedID,FType,FBreedNumber,FBreedName,FFenceID,FFenceName,”
+ “FPersonid,FGive,FZren,FNote,FCreatorID,FCreateTime,FVoucherID) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)”;
Object[] args = new Object[] { db.getFBillNo(), db.getFDate(),
db.getFBreedID(), db.getFType(), db.getFBreedNumber(),
db.getFBreedName(), db.getFFenceID(), db.getFFenceName(),
db.getFPersonid(), db.getFGive(), db.getFZren(), db.getFNote(),
db.getFCreatorID(), db.getFCreateTime() ,db.getFVoucherID()};
this.save(sql, args);
}public void update(DeadBreed db) {
String sql = "update t_DeadBreed set FBillNo=?,FDate=?,FBreedID=?,FType=?,FBreedNumber=?,FBreedName=?,FFenceID=?,FFenceName=?, "
+ “FPersonid=?,FGive=?,FZren=?,FNote=?,FCreatorID=?,FCreateTime=?,FVoucherID=? where FID=?”;
Object[] args = new Object[] { db.getFBillNo(), db.getFDate(),
db.getFBreedID(), db.getFType(), db.getFBreedNumber(),
db.getFBreedName(), db.getFFenceID(), db.getFFenceName(),
db.getFPersonid(), db.getFGive(), db.getFZren(), db.getFNote(),
db.getFCreatorID(), db.getFCreateTime(),db.getFVoucherID(), db.getFID() };
this.update(sql, args);
}public void delete(DeadBreed db) {
String sql = “delete t_DeadBreed where FID=?”;
Object[] args = new Object[] { db.getFID() };
this.delete(sql, args);
}public void delete(int FID) {
String sql = “delete t_DeadBreed where FID=?”;
Object[] args = new Object[] { FID };
this.delete(sql, args);}
public List<?> getListDate() {
String sql = “select * from t_DeadBreed order by fdate”;
return this.getListDate(sql, className, null);
}public DeadBreed getDeadBreed(int FID) {
int i = 0;
String sql = “select count() from t_DeadBreed where FID=?";
Object[] args = new Object[] { FID };
i = this.getCounts(sql, args);
if (i > 0) {
sql = “select * from t_DeadBreed where FID=?”;
return (DeadBreed)this.getObject(sql, className, args);
} else {
return null;
}
}
public List<?> getListDeadBreed(Date fdate1, Date fdate2, String fnum1) { String sql = "select * from t_DeadBreed where (FDate>=? and FDate<=?) and FBreedNumber like ? order by fdate"; Object[] args = new Object[] { fdate1, fdate2, fnum1 }; return this.getListDate(sql, className, args); } public DeadBreed getDeadBreed(int FBreedID, int FFenceID) { String sql = "select * from t_DeadBreed where FBreedID=? and FFenceID=?"; Object[] args = new Object[] { FBreedID, FFenceID }; return (DeadBreed)this.getObject(sql, className, args); } public List<?> getListUnDeadBreed(int depid) {
String sql = “select FID,FBillNo,FDate,FType,FBreedID,FBreedNumber,FBreedName,FFenceID,FFenceName,FPersonid,FGive,FZren,FNote,FCreatorID,FCreateTime,FVoucherID from vw_UnDeadBreed where depid=? order by fdate”;
Object[] args = new Object[] { depid };
return this.getListDate(sql, className, args);
}
public List<?> getListUnDeadBreed(int depid, Date fdate1,
Date fdate2, String fnum1) {
String sql = “select FID,FBillNo,FDate,FType,FBreedID,FBreedNumber,FBreedName,FFenceID,FFenceName,FPersonid,FGive,FZren,FNote,FCreatorID,FCreateTime,FVoucherID from vw_UnDeadBreed where depid=? and (FDate>=? and FDate<=?) and FBreedNumber like ? order by fdate”;
Object[] args = new Object[] { depid, fdate1, fdate2, fnum1 };
return this.getListDate(sql, className, args);
}
public String getCurrentNum(String FName) {
int i = 0;
String sql = "select count() from t_DeadBreed where FBillNo like '%S”
+ FName + “%'”;
i = this.getCounts(sql,null);
if (i > 0) {
sql = “select max(FBillNo) from t_DeadBreed where FBillNO like '%S”
+ FName + “%'”;
String str = (String)this.getObject(sql, null, null);
int len = str.length();
if (len > 9) {
String str1 = str.substring(9, str.length());
int k = Integer.parseInt(str1);
k++;
String str2 = k + “”;
String str3 = “”;
for (int j = 0; j < 3 - str2.length(); j++) {
str3 = str3 + “0”;
}
str3 = str3 + k;
return str3;
} else {
return “001”;
}
} else {
return “001”;
}
}public boolean hasBiller(String fuserid,int FID) {
int i = 0;
String sql = “select count(*) from t_DeadBreed where FCreatorID=? AND FID=?”;
Object[] args = new Object[] { fuserid , FID};
i = this.getCounts(sql, args);
if (i > 0)
return true;
else
return false;}
}
七,测试例子:
package com.xlkj.test;
import java.util.Iterator;
import java.util.List;
import com.xlkj.model.DeadBreed;
import com.xlkj.service.DeadBreedService;
/**
-
@author 余中伦 E-mail:371891632@qq.com
-
@version 创建时间:2022-6-20 下午7:44:26 类说明:
*/
public class TestDemo01 {
public static void main(String[] args) throws Exception {
DeadBreedService dbs=new DeadBreedService();
List<?> list=dbs.getListDate(); for(Iterator<?> iter=list.iterator();iter.hasNext()😉{
DeadBreed db=(DeadBreed)iter.next();
System.out.println(db.getFBillNo()+“:”+db.getFBreedName());
}}
}
看到测试中的语句,是不是没有SQL数据。只有类对象数据。每一层负责自己的任务,不想干扰。这样效率来看高。