/**
* 执行可变参数的SQL语句，进行保存、删除或更新操作
*
* @param sql 要执行的sql语句，?的赋值顺序必须与args数组的顺序相同
* @param args 要赋值的参数列表
* @return 操作结果，正数 成功，0 失败
*/
public int saveOrUpdate(String sql, Object... args) {
this.init();
if (conn == null) {
conn = JDBCUtil.getConn();
if ((conn = JDBCUtil.getConn()) == null) {
logger.error("getConn()失败！");
return 0;
}
}
try {
ps = conn.prepareStatement(sql);
for (int j = 0; j < args.length; j++) {
ps.setObject(j + 1, args[j]);
}
logger.info("saveOrUpdate()成功！");
return ps.executeUpdate();
} catch (Exception e) {
logger.error("sql执行失败" + e);
e.printStackTrace();
} finally {
// JDBCUtil.realse(conn, ps, null);
}
logger.error("saveOrUpdate()失败！");
return 0;
}
public double deposit(int accno, double amt) {
double nbal = 0.0;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = JDBCUtil.getOracleConnection();
ps = con.prepareStatement("select bal from accounts where accno=?");
ps.setInt(1, accno);
rs = ps.executeQuery();
if (rs.next()) {
double bal1 = rs.getDouble(1);
nbal = bal1 + amt;
ps = con.prepareStatement("update accounts set bal = ? where accno = ? ");
ps.setDouble(1, nbal);
ps.setInt(2, accno);
ps.executeUpdate();
} else {
throw new InvalidAccountNumberException();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.cleanup(con, ps, rs);
}
return nbal;
}
public PessoaFisica findCpf(String Cpf) {
try {
PreparedStatement p =
JDBCUtil.getConnection().prepareStatement("select * from tb_pessoa_fisica where cpf = ?");
p.setString(1, Cpf);
ResultSet res = p.executeQuery();
if (res.next()) {
return new PessoaFisica(
res.getLong("id"),
res.getString("nome"),
res.getString("endereco"),
res.getString("telefone"),
res.getString("cpf"),
res.getString("email"),
res.getDate("data_nascimento"),
res.getString("sexo"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
return null;
}
@Override
public List<PessoaFisica> findAll() {
List<PessoaFisica> lista = new ArrayList<PessoaFisica>();
try {
ResultSet res =
JDBCUtil.getConnection()
.prepareStatement("select * from tb_pessoa_fisica")
.executeQuery();
while (res.next()) {
lista.add(
new PessoaFisica(
res.getLong("id"),
res.getString("nome"),
res.getString("endereco"),
res.getString("telefone"),
res.getString("cpf"),
res.getString("email"),
res.getDate("data_nascimento"),
res.getString("sexo")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
return lista;
}
/**
* 查询符合条件的记录数
*
* @param sql 要执行的sql语句
* @param args 给sql语句中的？赋值的参数列表
* @return 符合条件的记录数
*/
public long getCount(String sql, Object... args) {
this.init();
if (conn == null) {
conn = JDBCUtil.getConn();
if ((conn = JDBCUtil.getConn()) == null) {
logger.error("getConn()失败！");
return 0L;
}
}
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
if (rs.next()) {
return rs.getLong(1); // 返回结果集条数
}
} catch (SQLException e) {
logger.error("sql执行失败" + e);
e.printStackTrace();
} finally {
// JDBCUtil.realse(conn, ps, rs);
}
return 0L;
}
@Override
public void delete(Aluno t) {
try {
PreparedStatement p =
JDBCUtil.getConnection().prepareStatement("delete from tb_aluno where id = ?");
p.setLong(1, t.getId());
p.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public void delete(PessoaFisica t) {
// TODO Auto-generated method stub
try {
PreparedStatement p =
JDBCUtil.getConnection().prepareStatement("delete from tb_pessoa_fisica where id = ?");
p.setLong(1, t.getId());
p.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public void delete(CEP t) {
try {
String sql = "DELETE FROM tb_cep WHERE cep = ?";
PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
qb.setLong(1, t.getCep());
qb.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
/**
* Checks if the value of the param argument is a valid parameter position.
*
* <p>
*
* @param param position to check
* @throws SQLException if the value of the param argument is not a valid parameter position
*/
void checkRange(int param) throws SQLException {
if (param < 1 || param > parameterCount) {
String msg = param + " is out of range";
throw JDBCUtil.outOfRangeArgument(msg);
}
}
// #ifdef JAVA6
@SuppressWarnings("unchecked")
public <T> T unwrap(java.lang.Class<T> iface) throws java.sql.SQLException {
if (isWrapperFor(iface)) {
return (T) this;
}
throw JDBCUtil.invalidArgument("iface: " + iface);
}
@Override
public void insert(CEP t) {
try {
String sql = "INSERT INTO tb_cep" + "(cep, endereco, cidade)" + "VALUES(?, ?, ?)";
PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
qb.setLong(1, t.getCep());
qb.setString(2, t.getEndereco());
qb.setString(3, t.getCidade());
qb.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public List<CEP> findAll() {
List<CEP> listaCep = new ArrayList<CEP>();
try {
String sql = "select * from tb_cep";
ResultSet res = JDBCUtil.getConnection().prepareStatement(sql).executeQuery();
while (res.next()) {
listaCep.add(
new CEP(res.getLong("cep"), res.getString("endereco"), res.getString("cidade")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
return listaCep;
}
/**
* 以对象的形式保存或更新一个实体
*
* @param sql 要执行的sql语句
* @param object 要保存或更新的实体对象
* @param args 不需要赋值的列标组成的数组，例如sql语句 "insert into tbl_user values(seq_user.nextval,?,?,?)"应为1
* @return 操作结果，1 成功，0 失败
*/
public int saveEntity(String sql, Object object, int... args) {
this.init();
if (conn == null) {
conn = JDBCUtil.getConn();
if ((conn = JDBCUtil.getConn()) == null) {
logger.error("getConn()失败！");
return 0;
}
}
try {
ps = conn.prepareStatement(sql);
Class<? extends Object> c = object.getClass();
Field[] fields = object.getClass().getDeclaredFields();
int temp = 1; // 正赋值的？的下标，最大下标为args的长度
int colIndex = 1; // SQL语句中的当前字段下标
int t = 0; // args数组的下标
for (int j = 0; j < fields.length; j++) {
Field field = fields[j]; // 得到某个声明属性
String methodName =
"get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Method method = c.getMethod(methodName); // 得到了当前类中的一个method
String rType = field.getType().getSimpleName().toString();
if (t < args.length && colIndex == args[t]) {
t++;
} else if ("int".equals(rType) || "INTEGER".equals(rType)) {
ps.setInt(temp++, (Integer) method.invoke(object));
} else {
ps.setObject(temp++, method.invoke(object));
}
colIndex++; // 更新索引下标
}
logger.info("saveEntity()插入成功！");
return ps.executeUpdate();
} catch (Exception e) {
logger.error("sql执行失败" + e);
e.printStackTrace();
} finally {
// JDBCUtil.realse(conn, ps, null);
}
logger.error("saveEntity()插入失败！");
return 0;
}
@Override
public void insert(Aluno t) {
try {
PreparedStatement p =
JDBCUtil.getConnection()
.prepareStatement(
"insert into tb_aluno"
+ " (nome, cpf, matricula, data_aniversario)"
+ "values (?,?,?,?)");
p.setString(1, t.getNome());
p.setString(2, t.getCpf());
p.setString(3, t.getMatricula());
p.setString(4, df.format(t.getAniversario()));
p.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public void update(CEP t) {
try {
String sql = "UPDATE tb_cep SET" + " cep = ?, endereco = ?, cidade = ? WHERE cep = ?";
PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
qb.setLong(1, t.getCep());
qb.setString(2, t.getEndereco());
qb.setString(3, t.getCidade());
qb.setLong(4, t.getCep());
qb.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public void update(Aluno t) {
try {
PreparedStatement p =
JDBCUtil.getConnection()
.prepareStatement(
"update tb_aluno set nome = ?, cpf = ?,"
+ " matricula = ?, data_aniversario = ? "
+ "where id = ?");
p.setString(1, t.getNome());
p.setString(2, t.getCpf());
p.setString(3, t.getMatricula());
p.setString(4, df.format(t.getAniversario()));
p.setLong(5, t.getId());
p.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public CEP find(Long id) {
try {
String sql = "select * from tb_cep WHERE cep = ?";
PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
qb.setLong(1, id);
ResultSet res = qb.executeQuery();
if (res.next()) {
return new CEP(res.getLong("cep"), res.getString("endereco"), res.getString("cidade"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
return null;
}
public List<CEP> findCep(String endereco) {
List<CEP> listaCep = new ArrayList<CEP>();
try {
String sql = "select * from tb_cep WHERE endereco LIKE ?";
PreparedStatement qb = JDBCUtil.getConnection().prepareStatement(sql);
qb.setString(1, "%" + endereco + "%");
ResultSet res = qb.executeQuery();
while (res.next()) {
listaCep.add(
new CEP(res.getLong("cep"), res.getString("endereco"), res.getString("cidade")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
return listaCep;
}
public Aluno find(String nome) {
try {
PreparedStatement p =
JDBCUtil.getConnection().prepareStatement("select * from tb_aluno where nome like ?");
p.setString(1, nome + "%");
ResultSet res = p.executeQuery();
if (res.next()) {
return new Aluno(
res.getLong("id"),
res.getString("nome"),
res.getString("cpf"),
res.getString("matricula"),
res.getDate("data_aniversario"));
}
} catch (Exception e) {
// TODO: handle exception
} finally {
JDBCUtil.closeConnection();
}
return null;
}
@Override
public void insert(PessoaFisica t) {
try {
PreparedStatement p =
JDBCUtil.getConnection()
.prepareStatement(
"insert into tb_pessoa_fisica (nome, endereco, telefone, cpf, email, data_nascimento, sexo) "
+ "values (?,?,?,?,?,?,?)");
p.setString(1, t.getNome());
p.setString(2, t.getEndereco());
p.setString(3, t.getTelefone());
p.setString(4, t.getCpf());
p.setString(5, t.getEmail());
p.setString(6, df.format(t.getDataNascimento()));
p.setString(7, t.getSexo());
p.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public void update(PessoaFisica t) {
try {
PreparedStatement p =
JDBCUtil.getConnection()
.prepareStatement(
"update tb_pessoa_fisica set nome = ?, endereco = ?, telefone = ?, cpf = ?, email = ?, data_nascimento = ?, sexo = ? "
+ "where id = ?");
p.setString(1, t.getNome());
p.setString(2, t.getEndereco());
p.setString(3, t.getTelefone());
p.setString(4, t.getCpf());
p.setString(5, t.getEmail());
p.setString(6, df.format(t.getDataNascimento()));
p.setString(7, t.getSexo());
p.setLong(8, t.getId());
p.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
}
@Override
public List<Aluno> findAll() {
ArrayList<Aluno> lista = new ArrayList<Aluno>();
try {
ResultSet res =
JDBCUtil.getConnection().prepareStatement("select * from tb_aluno").executeQuery();
while (res.next()) {
lista.add(
new Aluno(
res.getLong("id"),
res.getString("nome"),
res.getString("cpf"),
res.getString("matricula"),
res.getDate("data_aniversario")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.closeConnection();
}
return lista;
}
public static ResultSet executeQuery(String sql) {
return JDBCUtil.executeQuery(sql, new Object[] {});
}
private void init() {
if (this.conn == null) {
this.conn = JDBCUtil.getConn();
}
}
public static int executeUpdate(String sql, Object[] objs) {
return JDBCUtil.executeUpdate(sql, objs);
}
private Result newColumnResult(long position, int count) throws SQLException {
int mArraySize = 0;
String mArrayStruct = data.getTypeStructure();
ArrayList<Integer> dimSize = getDimSize(mArrayStruct);
ArrayList<Integer> iterIndex = new ArrayList<>(); // go through all the array values
for (int i = 0; i < dimSize.size(); i++) {
mArraySize += dimSize.get(i);
}
// tests if the required elements exist. The limits are determined
// by computing the product of each dimension's size
if (!JDBCClobClient.isInLimits(mArraySize, position, count)) {
throw JDBCUtil.outOfRangeArgument();
}
Type[] types = new Type[2];
types[0] = Type.SQL_VARCHAR;
types[1] = elementType;
ResultMetaData meta = ResultMetaData.newSimpleResultMetaData(types);
meta.columnLabels = new String[] {"C1", "C2"};
meta.colIndexes = new int[] {-1, -1};
meta.columns = new ColumnBase[2];
ColumnBase column = new ColumnBase("", "", "", "");
column.setType(types[0]);
meta.columns[0] = column;
column = new ColumnBase("", "", "", "");
column.setType(types[1]);
meta.columns[1] = column;
RowSetNavigatorClient navigator = new RowSetNavigatorClient();
for (int i = (int) position; i < position + count; i++) {
Object[] rowData = new Object[2];
rowData[0] = Integer.valueOf(i + 1);
try {
rowData[1] = data.getCell(new RasPoint(1, 1));
} catch (RasDimensionMismatchException | RasIndexOutOfBoundsException e) {
System.err.println("Can't get the cell at point (1, 1)");
e.printStackTrace();
}
navigator.add(rowData);
}
Result result = Result.newDataResult(meta);
result.setNavigator(navigator);
return result;
}
/**
* 释放连接资源
*
* @param conn 数据库连接池
* @param ps PreparedStatement
* @param rs ResultSet
*/
public void release() {
JDBCUtil.realse(conn, ps, rs);
}
@Test
public void testQuery() throws Exception {
JDBCUtil.executeQuery(conn, "SELECT * FROM G1");
}
/**
* 查询实体对象的，并封装到一个集合
*
* @param <T> 要查询的对象的集合
* @param sql 要执行的sql语句
* @param clazz 要查询的对象的类型
* @param args 给sql语句中的？赋值的参数列表
* @return 要查询的类的集合，无结果时返回null
*/
@SuppressWarnings("unchecked")
public <T> List<T> executeQuery(String sql, Class<T> clazz, Object... args) {
this.init();
List<Object> list = new ArrayList<Object>();
if (conn == null) {
conn = JDBCUtil.getConn();
if ((conn = JDBCUtil.getConn()) == null) {
logger.error("getConn()失败！");
return null;
}
}
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
Field[] fs = clazz.getDeclaredFields(); // 获得clazz类的所有声明的字段
String[] colNames = new String[fs.length]; // 保存属性名
String[] rTypes = new String[fs.length]; // 属性类型
Method[] methods = clazz.getMethods();
while (rs.next()) {
for (int i = 0; i < fs.length; i++) {
Field f = fs[i];
String colName = f.getName().substring(0, 1).toUpperCase() + f.getName().substring(1);
// System.out.println(colName);
colNames[i] = colName;
String rType = f.getType().getSimpleName();
rTypes[i] = rType;
}
Object object = (T) clazz.newInstance();
for (int i = 0; i < colNames.length; i++) {
String colName = colNames[i];
String methodName = "set" + colName;
// 查找并调用对应的setter方法赋
for (Method m : methods) {
if (methodName.equals((m.getName()))) {
// 如果抛了参数不匹配异常，检查JavaBean中该属性类型，并添加else分支进行处理
if ("int".equals(rTypes[i]) || "Integer".equals(rTypes[i])) {
m.invoke(object, rs.getInt(colName));
} else if ("Date".equals(rTypes[i])) {
m.invoke(object, rs.getDate(colName));
} else if ("Timestamp".equals(rTypes[i])) {
m.invoke(object, rs.getTimestamp(colName));
} else {
m.invoke(object, rs.getObject(colName));
}
break;
}
}
}
list.add(object);
}
logger.info("executeQuery()成功！");
return (List<T>) list;
} catch (Exception e) {
logger.error("sql执行失败" + e);
e.printStackTrace();
} finally {
// JDBCUtil.realse(conn, ps, rs);
}
logger.error("executeQuery()失败！");
return null;
}
private void checkClosed() throws SQLException {
if (closed) {
throw JDBCUtil.sqlException(ErrorCode.X_07501);
}
}