附:程序代码点击下载
1. 结构化数据库代码 程序结构:
User.java是bean类.
JdbcUtils.java是数据库工具类,包含数据库驱动加载,连接数据库,数据库资源释放。
db.properties是数据库的配置信息,包含数据库url,用户名和密码。将其分离出来,便于底层数据库的更改。
Demo3_insert.java包含数据库增删查改操作,将他们进行了封装。
2.具体代码:
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mytest
username=root
password=root
JdbcUtils.java
package com.hongwei.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils {
private static Properties config = new Properties();
//静态代码块,只执行一次,类加载时执行
static{
try {
config.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));
System.out.println(config.getProperty("driver"));
Class.forName(config.getProperty("driver"));
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(config.getProperty("url"),config.getProperty("username"),config.getProperty("password"));
}
public static void release(ResultSet rs,Statement st,Connection conn){
if(rs!=null){
try{
rs.close();
}catch(Exception e){
e.printStackTrace();
}
rs = null;
}
if(st!=null){
try{
st.close();
}catch(Exception e){
e.printStackTrace();
}
st = null;
}
if(conn!=null){
try{
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}
Demo3_insert.java
package com.hongwei.jdbc;
import com.hongwei.bean.User;
import com.hongwei.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.junit.Test;
import com.hongwei.utils.JdbcUtils;
public class Demo3_insert {
public void insert() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql ="insert into users(id,name,password,email,birthday) values('4','zhouqiqi','123456','123@qq.com','1992-10-14')";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!");
}
}finally{
JdbcUtils.release(rs, st, conn);
}
}
public void update() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "update users set name='zhouxiaowei'";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功!");
}
}finally{
JdbcUtils.release(rs, st, conn);
}
}
public void delete() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql ="delete from users where id='1'";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功!");
}
}finally{
JdbcUtils.release(rs, st, conn);
}
}
@Test
public void getAll() throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql ="select * from users";
rs = st.executeQuery(sql);
ArrayList<User> list = new ArrayList<User>();
while(rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setDate(rs.getDate("birthday"));
list.add(user);
}
System.out.println(list);
}finally{
JdbcUtils.release(rs, st, conn);
}
}
}
User.java
package com.hongwei.bean;
import java.sql.Date;
public class User {
private int id;
private String name;
private String password;
private String email;
private Date date;
public User(int id, String name, String password, String email, Date date) {
super();
this.id = id;
this.name = name;
this.password = password;
this.email = email;
this.date = date;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password
+ ", email=" + email + ", date=" + date + "]";
}
}
注:若要求改为oracle数据库,只需修改db.properties。此外,对应的sql语句可能也要修改。