java数据库后台设计

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

<?xml version="1.0"?>

<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数据。只有类对象数据。每一层负责自己的任务，不想干扰。这样效率来看高。