jdbc入门到精通2.1实操账户表增删改查

[1]创建表和配制文件

/cn/net/trimmer/jdbc/sql/db.properties

-- Create accounts , use test jdbc-demo project
create table ACCOUNTS
(
  accountno   NUMBER(10) not null,
  accountname VARCHAR2(30),
  password    VARCHAR2(11),
  balance     NUMBER(10,2),
  opendate    DATE
);
-- Add comments to the table 
comment on table ACCOUNTS is 'jdbc 测试-账户信息表';
-- Add comments to the columns 
comment on column ACCOUNTS.accountno is '账户编码';
comment on column ACCOUNTS.accountname is '账户名称';
comment on column ACCOUNTS.password is '账户密码';
comment on column ACCOUNTS.balance  is '账户余额';
comment on column ACCOUNTS.opendate is '账户开户时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table ACCOUNTS add constraint ACCOUNTNO primary key (ACCOUNTNO);
--------------------------------------------------------------------
-- Create sequence 
create sequence SEQ_ACCOUNT
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
--------------------------------------------------------------------
-- 增加一列
-- insert into ACCOUNTS values(seq_account.nextval,?,?,?,?)
-- 删除一列
--delete from ACCOUNTS where accountno = ?
-- 修改一列
--update ACCOUNTS set accountname = ?, password = ?, balance = ? ,  openDate = ? where accountno = ?
-- 查询一列
select accountno,accountname,password,balance,opendate from ACCOUNTS where accountno = '42';

/cn/net/trimmer/jdbc/conf/db.properties

jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.230.10:1521:orcl
jdbc.username=username
jdbc.password=password

[2]创建实体类并引入工具类

/cn/net/trimmer/jdbc/entity/Account.java

package cn.net.trimmer.jdbc.entity;

import java.util.Date;

/**
 * 账户表
 * 
 * @author wl
 *
 */
public class Account {
	private String accountNo;
	private String accountName;
	private String password;
	private Double balance;
	private Date openDate;

	public Account() {
		super();
	}

	public Account(String accountNo, String accountName, String password, Double balance, Date openDate) {
		super();
		this.accountNo = accountNo;
		this.accountName = accountName;
		this.password = password;
		this.balance = balance;
		this.openDate = openDate;
	}

	public String getAccountNo() {
		return accountNo;
	}

	public void setAccountNo(String accountNo) {
		this.accountNo = accountNo;
	}

	public String getAccountName() {
		return accountName;
	}

	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Double getBalance() {
		return balance;
	}

	public void setBalance(Double balance) {
		this.balance = balance;
	}

	public Date getOpenDate() {
		return openDate;
	}

	public void setOpenDate(Date openDate) {
		this.openDate = openDate;
	}

	@Override
	public String toString() {
		return "Account [accountNo=" + accountNo + ", accountName=" + accountName + ", password=" + password + ", balance=" + balance + ", openDate=" + openDate + "]";
	}
}

/cn/net/trimmer/jdbc/util/JdbcUtils.java

package cn.net.trimmer.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * jdbc 工具类简单抽取
 * 
 * @author wl
 *
 */
public class JdbcUtils {
	private static final Properties prop = new Properties();
	// 类加载时读取配置文件
	static {
		InputStream in = null;
		try {
			// 1. 从类路径加载配置文件
			in = JdbcUtils.class.getResourceAsStream("/cn/net/trimmer/jdbc/conf/db.properties");
			// 2. 解析配置文件
			prop.load(in);
			System.out.println("数据库配置文件加载完成...");
		} catch (IOException e) {
			e.printStackTrace();
			if (in != null) {
				try {
					in.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
		}
	}

	/**
	 * 获取数据库连接对象
	 * 
	 * @return conn 数据库连接对象
	 */
	public static Connection getConn() {
		Connection conn = null;
		try {
			String driver = prop.getProperty("jdbc.driver");
			String url = prop.getProperty("jdbc.url");
			String username = prop.getProperty("jdbc.username");
			String password = prop.getProperty("jdbc.password");
			//加载驱动
			Class.forName(driver);
			//获得连接对象
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return conn;
	}

	/**
	 * 释放资源
	 * 
	 * @param rs   执行结果集
	 * @param stm  sql操作对象
	 * @param conn 数据库连接
	 */
	public static void release(ResultSet rs, Statement stm, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
		if (stm != null) {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
	}
}

[3]创建dao接口

/cn/net/trimmer/jdbc/dao/AccountDao.java

package cn.net.trimmer.jdbc.dao;

import cn.net.trimmer.jdbc.entity.Account;

public interface AccountDao {
	//增
	boolean insertAccount(Account account);
	//删
	boolean deleteAccountByAccountNo(String no);
	//改
	boolean updateAccount(Account account);
	//查
	Account selectOne(String no);	
}

[4]创建dao实现类

/cn/net/trimmer/jdbc/dao/impl/AccountDaoImpl.java

package cn.net.trimmer.jdbc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import cn.net.trimmer.jdbc.dao.AccountDao;
import cn.net.trimmer.jdbc.entity.Account;
import cn.net.trimmer.jdbc.util.JdbcUtils;

public class AccountDaoImpl implements AccountDao {

	@Override
	public boolean insertAccount(Account account) {
		Connection conn = null;
		PreparedStatement pstm = null;
		boolean rs = false;
		try {
			conn = JdbcUtils.getConn();
			String sql = "insert into ACCOUNTS values(seq_account.nextval,?,?,?,?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, account.getAccountName());
			pstm.setString(2, account.getPassword());
			pstm.setDouble(3, account.getBalance());
			pstm.setDate(4, new java.sql.Date(account.getOpenDate().getTime()));
			int i = pstm.executeUpdate();
			if (i > 0)
				rs = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.release(null, pstm, conn);
		}
		return rs;
	}

	@Override
	public boolean deleteAccountByAccountNo(String no) {
		Connection conn = null;
		PreparedStatement pstm = null;
		boolean rs = false;
		try {
			conn = JdbcUtils.getConn();
			String sql = "delete from ACCOUNTS where accountno = ?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, no);
			int i = pstm.executeUpdate();
			if (i > 0)
				rs = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.release(null, pstm, conn);
		}
		return rs;
	}

	@Override
	public boolean updateAccount(Account account) {
		Connection conn = null;
		PreparedStatement pstm = null;
		boolean rs = false;
		try {
			conn = JdbcUtils.getConn();
			String sql = "update ACCOUNTS set accountname = ?, password = ?, balance = ? ,  openDate = ? where accountno = ?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, account.getAccountName());
			pstm.setString(2, account.getPassword());
			pstm.setDouble(3, account.getBalance());
			pstm.setDate(4, new java.sql.Date(account.getOpenDate().getTime()));
			pstm.setString(5, account.getAccountNo());
			int i = pstm.executeUpdate();
			if (i > 0)
				rs = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.release(null, pstm, conn);
		}
		return rs;
	}

	@Override
	public Account selectOne(String no) {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Account acc = null;
		try {
			conn = JdbcUtils.getConn();
			String sql = "select accountno,accountname,password,balance,opendate from ACCOUNTS where accountno = ?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, no);
			rs = pstm.executeQuery();
			while (rs.next()) {
				String accountNo = rs.getString("accountno");
				String accountName = rs.getString("accountname");
				String password = rs.getString("password");
				Double balance = rs.getDouble("balance");
				Date openDate = rs.getDate("opendate");
				acc = new Account(accountNo, accountName, password, balance, openDate);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.release(rs, pstm, conn);
		}
		return acc;
	}
}

[5]单元测试

/cn/net/trimmer/jdbc/test/TestAccountDao.java

package cn.net.trimmer.jdbc.test;

import java.util.Date;

import org.junit.Test;

import cn.net.trimmer.jdbc.dao.AccountDao;
import cn.net.trimmer.jdbc.dao.impl.AccountDaoImpl;
import cn.net.trimmer.jdbc.entity.Account;

/**
 * 测试增删改查的方法
 * 
 * @author wl
 *
 */
public class TestAccountDao {	
	static final AccountDao dao = new AccountDaoImpl();

	@Test
	public void testUpdateAccount() {
		Account account = new Account("61", "蘑菇头老爸", "100000", 8000.0, new Date());
		boolean b = dao.updateAccount(account);
		if(b) {
			System.out.println("修改成功");
		}else {
			System.out.println("修改失败");
		}
	}

	@Test
	public void testDeleteAccountByNo() {
		boolean b = dao.deleteAccountByAccountNo("42");
		if(b) {
			System.out.println("删除成功");
		}else {
			System.out.println("删除失败");
		}
	}
	
	@Test
	public void testInsert() {
		Account account = new Account("", "蘑菇头", "123456", 3000.0, new Date());
		boolean b = dao.insertAccount(account);
		if(b) {
			System.out.println("插入成功");
		}else {
			System.out.println("插入失败");
		}
	}
	
	@Test
	public void testSelectOne() {
		Account account = dao.selectOne("43");
		System.out.println(account);
	}
}

[6]demo下载地址

https://github.com/wanglei199809/jdbc-demo2.git