JDBC的基本概念
(1)连接数据库的桥梁(2)由java语言编写的类和接口组成
(3)可以为多种数据库提供统一的访问
模型层(DB + DAO):封装了实现类
控制层:action层,负责调用模型层
视图层调用控制层
JDBC各种连接方式的对比:
1、JDBC + ODBC桥的方式。特点:需要数据库的ODBC驱动,仅适用于微软的系统这种方式,JDBC将调用传递给ODBC,然后ODBC再调用本地的数据库驱动代码。
2、JDBC + 厂商API的形式。特点:厂商API一般使用C编写
这种方式,JDBC将调用直接传递给厂商API的服务,然后在调用本地的数据库驱动。
3、JDBC + 厂商Database Connection Server + DataBase的形式。
特点:在JAVA与DATABASE之间架起了一台专门用于数据库连接的服务器(一般有数据库厂商提供)
这种方式,JDBC将调用传递给中间服务器,中间服务器再将调用转换成数据库能够被调用的形式,在调用数据库服务器。中间增设数据库服务器能够提升效率,但不如直接操作数据库便捷。
4、JDBC + DATABASE的连接方式。
特点:这使得Application与数据库分开
1.Database DBUtil.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc?useUnicode=true&characterEncoding=utf-8";
private static final String USER="root";
private static final String PASSWORD="123456";
static Connection conn=null;
static{
try {
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
conn=DriverManager.getConnection(URL, USER, PASSWORD);
//通过数据库的连接操作数据库,实现增删改查
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
return conn;
}
}
2.Model Goddess.java
import java.util.Date;
public class Goddess {
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String userName) {
user_name = userName;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getCreate_user() {
return create_user;
}
public void setCreate_user(String createUser) {
create_user = createUser;
}
public String getUpdate_user() {
return update_user;
}
public void setUpdate_user(String updateUser) {
update_user = updateUser;
}
public Date getCreate_date() {
return create_date;
}
public void setCreate_date(Date createDate) {
create_date = createDate;
}
public Date getUpdate_date() {
return update_date;
}
public void setUpdate_date(Date updateDate) {
update_date = updateDate;
}
public Integer getIsdel() {
return isdel;
}
public void setIsdel(Integer isdel) {
this.isdel = isdel;
}
private Integer id;
private String user_name;
private Integer sex;
private Integer age;
private Date birthday;
private String email;
private String mobile;
private String create_user;
private String update_user;
private Date create_date;
private Date update_date;
private Integer isdel;
}
3.Control GoddessDao
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.List;
public class GoddessDao {
public void addGod(Goddess g) throws SQLException{
Connection conn=DBUtil.getConnection();
String sql="insert into imooc_goddess(user_name,sex,age,birthday,email,mobile,create_user,create_date,update_user,update_date,isdel)"+
"values(?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, g.getUser_name());
ptmt.setInt(2, g.getSex());
ptmt.setInt(3, g.getAge());
ptmt.setDate(4, new Date(g.getBirthday().getTime()));
ptmt.setString(5, g.getEmail());
ptmt.setString(6, g.getMobile());
ptmt.setString(7, g.getCreate_user());
ptmt.setString(8, g.getUpdate_user());
ptmt.setInt(9, g.getIsdel());
ptmt.execute();
}
public void update(Goddess g) throws SQLException{
Connection conn=DBUtil.getConnection();
String sql= " update imooc_goddess " +
" set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?, " +
" update_user=?,update_date=current_date(),isdel=? "+
" where id=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, g.getUser_name());
ptmt.setInt(2, g.getSex());
ptmt.setInt(3, g.getAge());
ptmt.setDate(4, new Date(g.getBirthday().getTime()));
ptmt.setString(5, g.getEmail());
ptmt.setString(6, g.getMobile());
ptmt.setString(7, g.getUpdate_user());
ptmt.setInt(8, g.getIsdel());
ptmt.setInt(9, g.getId());
ptmt.execute();
}
public void delete(Integer id) throws SQLException{
Connection conn=DBUtil.getConnection();
String sql= " delete from imooc_goddess " +
" where id=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setInt(1, id);
ptmt.execute();
}
public List<Goddess> query() throws SQLException {
List<Goddess> gods=new ArrayList<Goddess>();
Connection conn=DBUtil.getConnection();
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery("select id, user_name ,age from imooc_goddess");
while(rs.next()){
Goddess g= new Goddess();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
gods.add(g);
}
return gods;
}
public Goddess get(Integer id) throws SQLException {
Goddess g=null;
Connection conn=DBUtil.getConnection();
String sql= " select * from imooc_goddess " +
" where id=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setInt(1, id);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
g=new Goddess();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
g.setSex(rs.getInt("sex"));
g.setBirthday(rs.getDate("birthday"));
g.setEmail(rs.getString("email"));
g.setMobile(rs.getString("mobile"));
g.setCreate_date(rs.getDate("create_date"));
g.setCreate_user(rs.getString("create_user"));
g.setUpdate_date(rs.getDate("update_date"));
g.setUpdate_user(rs.getString("update_user"));
g.setIsdel(rs.getInt("isdel"));
}
return g;
}
public List<Goddess> get(String name) throws SQLException {
List<Goddess> result=new ArrayList<Goddess>();
Connection conn=DBUtil.getConnection();
String sql= " select * from imooc_goddess " +
" where user_name=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, name);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
Goddess g=new Goddess();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
g.setSex(rs.getInt("sex"));
g.setBirthday(rs.getDate("birthday"));
g.setEmail(rs.getString("email"));
g.setMobile(rs.getString("mobile"));
g.setCreate_date(rs.getDate("create_date"));
g.setCreate_user(rs.getString("create_user"));
g.setUpdate_date(rs.getDate("update_date"));
g.setUpdate_user(rs.getString("update_user"));
g.setIsdel(rs.getInt("isdel"));
result.add(g);
}
return result;
}
public List<Goddess> get(Integer id,String name) throws SQLException {
List<Goddess> result=new ArrayList<Goddess>();
Connection conn=DBUtil.getConnection();
String sql= " select * from imooc_goddess " +
" where id=? and user_name=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setInt(1, id);
ptmt.setString(2, name);
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
Goddess g=new Goddess();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
g.setSex(rs.getInt("sex"));
g.setBirthday(rs.getDate("birthday"));
g.setEmail(rs.getString("email"));
g.setMobile(rs.getString("mobile"));
g.setCreate_date(rs.getDate("create_date"));
g.setCreate_user(rs.getString("create_user"));
g.setUpdate_date(rs.getDate("update_date"));
g.setUpdate_user(rs.getString("update_user"));
g.setIsdel(rs.getInt("isdel"));
result.add(g);
}
return result;
}
public List<Goddess> query(List<Map<String, Object>> params) throws SQLException {
List<Goddess> result=new ArrayList<Goddess>();
Connection conn=DBUtil.getConnection();
StringBuilder sb=new StringBuilder();
sb.append(" select * from imooc_goddess where 1=1");
if(params!=null && params.size()>0){
for (int i = 0; i < params.size(); i++) {
Map<String, Object> map=params.get(i);
sb.append(" and "+map.get("name")+" "+map.get("rela")+" "+map.get("value")+" ");
}
}
PreparedStatement ptmt=conn.prepareStatement(sb.toString());
ResultSet rs=ptmt.executeQuery();
while(rs.next()){
Goddess g=new Goddess();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
g.setSex(rs.getInt("sex"));
g.setBirthday(rs.getDate("birthday"));
g.setEmail(rs.getString("email"));
g.setMobile(rs.getString("mobile"));
g.setCreate_date(rs.getDate("create_date"));
g.setCreate_user(rs.getString("create_user"));
g.setUpdate_date(rs.getDate("update_date"));
g.setUpdate_user(rs.getString("update_user"));
g.setIsdel(rs.getInt("isdel"));
result.add(g);
}
return result;
}
}
GodessAction.java
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class GoddessAction {
public void add(Goddess goddess) throws Exception{
GoddessDao dao=new GoddessDao();
goddess.setSex(1);
goddess.setCreate_user("ADMIN");
goddess.setUpdate_user("ADMIN");
goddess.setIsdel(0);
dao.addGod(goddess);
}
public Goddess get(Integer id) throws SQLException{
GoddessDao dao=new GoddessDao();
return dao.get(id);
}
public void edit(Goddess goddess) throws Exception{
GoddessDao dao=new GoddessDao();
dao.update(goddess);
}
public void del(Integer id) throws SQLException{
GoddessDao dao=new GoddessDao();
dao.delete(id);
}
public List<Goddess> query() throws Exception{
GoddessDao dao=new GoddessDao();
return dao.query();
}
public List<Goddess> query(List<Map<String, Object>> params) throws Exception{
GoddessDao dao=new GoddessDao();
return dao.query(params);
}
}
4.View
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
public class View {
private static final String CONTEXT="欢迎来到女神禁区:\n" +
"下面是女神禁区的功能列表:\n" +
"[MAIN/M]:主菜单\n" +
"[QUERY/Q]:查看全部女神的信息\n" +
"[GET/G]:查看某位女神的详细信息\n" +
"[ADD/A]:添加女神信息\n" +
"[UPDATE/U]:更新女神信息\n" +
"[DELETE/D]:删除女神信息\n" +
"[SEARCH/S]:查询女神信息(根据姓名、手机号来查询)\n" +
"[EXIT/E]:退出女神禁区\n" +
"[BREAK/B]:退出当前功能,返回主菜单";
private static final String OPERATION_MAIN="MAIN";
private static final String OPERATION_QUERY="QUERY";
private static final String OPERATION_GET="GET";
private static final String OPERATION_ADD="ADD";
private static final String OPERATION_UPDATE="UPDATE";
private static final String OPERATION_DELETE="DELETE";
private static final String OPERATION_SEARCH="SEARCH";
private static final String OPERATION_EXIT="EXIT";
private static final String OPERATION_BREAK="BREAK";
public static void main(String[] args) {
System.out.println(CONTEXT);
//怎么保持程序一直运行
Scanner scan=new Scanner(System.in);
Goddess goddess=new Goddess();
GoddessAction action=new GoddessAction();
String prenious=null;
Integer step=1;
while(scan.hasNext()){
String in=scan.next().toString();
if(OPERATION_EXIT.equals(in.toUpperCase())
||OPERATION_EXIT.substring(0, 1).equals(in.toUpperCase())){
System.out.println("您已成功退出女神禁区。");
break;
}else if(OPERATION_QUERY.equals(in.toUpperCase())
||OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())){
try {
List<Goddess> list=action.query();
for (Goddess go : list) {
System.out.println(go.getId()+",姓名:"+go.getUser_name());
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(OPERATION_ADD.equals(in.toUpperCase())
||OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())
||OPERATION_ADD.equals(prenious)){
prenious=OPERATION_ADD;
//新增女神
if(1==step){
System.out.println("请输入女神的[姓名]");
}else if(2==step){
goddess.setUser_name(in);
System.out.println("请输入女神的[年龄]");
}else if(3==step){
goddess.setAge(Integer.valueOf(in));
System.out.println("请输入女神的[生日],格式如:yyyy-MM-dd");
}else if(4==step){
SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
Date birthday=null;
try {
birthday = sf.parse(in);
goddess.setBirthday(birthday);
System.out.println("请输入女神的[邮箱]");
} catch (ParseException e) {
e.printStackTrace();
System.out.println("您输入的格式有误,请重新输入");
step=3;
}
}else if(5==step){
goddess.setEmail(in);
System.out.println("请输入女神的[手机号]");
}else if(6==step){
goddess.setMobile(in);
try {
action.add(goddess);
System.out.println("新增女神成功");
} catch (Exception e) {
e.printStackTrace();
System.out.println("新增女神失败");
}
}
if(OPERATION_ADD.equals(prenious)){
step++;
}
}else{
System.out.println("您输入的值为:"+in);
}
}
}
}