初学JDBC

内容概要：

一、JDBC简介
二、使用详解
1、JDBC常用的接口
2、JDBC编程的步骤
3、执行SQL语句

一、JDBC是什么？

JDBC全称Java Data Base Connectivity（java数据库连接），可以为多种数据库提供统一的访问。
JDBC像一座桥梁，连接着java和数据库。有了JDBC，java才可以从数据库读取和存储数据。

---

二、JDBC使用详解
2、JDBC编程步骤
加载驱动程序：Class.forName(driverClass);
加载Mysql驱动：Class.forName(“com.mysql.jdbc.Driver”);
加载Oracle驱动：Class.forName(“oracle.jdbc.driver.OracleDriver”);

获得数据库连接：DriverManager.getConnection(“jdbc:mysql://127.0.0.1:3306/imooc”,”root”,”root”);

创建Statement对象：conn.createStatement();

---

Java连接数据库例子
// 必须要加入 mysql-connector-java-5.0.8.jar

package com.example.db;
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://192.168.191.1:3306/jdbctest";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 获得数据库的连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        // 3.通过数据库的连接操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from man");
        //有数据才会执行,可以循环获取全部数据
        while(rs.next()) {
            //根据列名得到数据
            System.out.println(rs.getString("name")+":"+rs.getString("age"));
        }
    }
}

---

MVC模型

---

架构

---

util工具类 用于获得connction

package com.example.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DButil {

    private static final String URL = "jdbc:mysql://192.168.191.1:3306/jdbctest?useUnicode=true&characterEncoding=utf-8";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public 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;
    }

}

---

View层 调用 Action

package com.example.view;


import com.example.action.ManAction;
import com.example.model.Man;

public class MainView {
    public static void main(String[] args) {


        ManAction action = new ManAction();
        Man man = new Man();
        man.setName("YoYo");
        man.setAge(19);
        action.DeleteMan(man);

    }

}

---

action层

package com.example.action;

import java.util.HashMap;
import java.util.List;

import com.example.dao.ManDao;
import com.example.model.Man;

public class ManAction {

    // 增
    public void addMan(Man man) {
        ManDao dao = new ManDao();
        dao.AddMan(man);
    }

    // 删
    public void DeleteMan(Man man) {
        ManDao dao = new ManDao();
        dao.DeleteMan(man);
    }

    // 改
    public void AlterMan(Man man) {
        ManDao dao = new ManDao();
        dao.AlterMan(man);

    }

    // 查
    public List<Man> queryMans() {
        ManDao dao = new ManDao();
        List<Man> list = dao.queryMans();
        return list;
    }

    // 查单个
    public Man getMan(String name) {
        ManDao dao = new ManDao();
        Man man = dao.getMan(name);
        return man;
    }

    // 带条件查
    public List<Man> queryMans(List<HashMap<String, String>> params) {
        // 在外部初始化params
        // List<HashMap<String, String>> params = new ArrayList<HashMap<String,
        // String>>();
        // HashMap<String, String> map1 = new HashMap<String,String>();
        // map1.put("name", "name");
        // map1.put("rela", "like");
        // map1.put("value", "%冬眠%");
        // params.add(map1);
        //
        // HashMap<String, String> map2 = new HashMap<String,String>();
        // map2.put("name", "age");
        // map2.put("rela", "=");
        // map2.put("value", "23");
        // params.add(map2);

        // 以这种方式可以不需要频繁的修改DAO方法, 只需要添加对应的HashMap
        ManDao dao = new ManDao();
        List<Man> list = dao.queryMans(params);
        return list;
    }

}

DAO 连接数据库

package com.example.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.example.db.DButil;
import com.example.model.Man;
import com.mysql.jdbc.PreparedStatement;

public class ManDao {

    //增
    public void AddMan(Man man)  {
        Connection conn = DButil.getConnection();
        try {
            String sql = "INSERT INTO man (name,age) values (?,?)";
            PreparedStatement ptmt =  (PreparedStatement) conn.prepareStatement(sql);
            ptmt.setString(1, man.getName());
            ptmt.setInt(2, man.getAge());
            ptmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //删
    public void DeleteMan(Man man) {
        Connection conn = DButil.getConnection();
        try {
            String sql = "DELETE from man where name = ?";// set age = ? , city = ?//逗号隔开
            PreparedStatement ptmt =  (PreparedStatement) conn.prepareStatement(sql);
            ptmt.setString(1, man.getName());
            ptmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //改
    public void AlterMan(Man man) {
        Connection conn = DButil.getConnection();
        try {
            String sql = "UPDATE  man set age = ? where name = ?";// set age = ? , city = ?//逗号隔开
            PreparedStatement ptmt =  (PreparedStatement) conn.prepareStatement(sql);
            ptmt.setInt(1, man.getAge());
            ptmt.setString(2, man.getName());
            ptmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //查
    public List<Man> queryMans() {
        List<Man> list = new ArrayList<Man>();
        Connection conn = DButil.getConnection();
        try {
            String sql = "select * from man";
            PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = ptmt.executeQuery();
            Man man = null;
            while(rs.next()) {
                man = new Man();
                man.setName(rs.getString("name"));
                man.setAge(rs.getInt("age"));
                list.add(man);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //带条件查
        public List<Man> queryMans(List<HashMap<String,String>> params) {
            List<Man> list = new ArrayList<Man>();
            Connection conn = DButil.getConnection();
            try {
                StringBuffer sql = new StringBuffer();
                sql.append( "select * from man where 1 = 1");
                for (HashMap<String, String> map : params) {
                    sql.append(" and "+map.get("name")+" "+map.get("rela")+" '"+map.get("value")+"'");
                }
                PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql.toString());
                ResultSet rs = ptmt.executeQuery();
                Man man = null;
                while(rs.next()) {
                    man = new Man();
                    man.setName(rs.getString("name"));
                    man.setAge(rs.getInt("age"));
                    list.add(man);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return list;
        }
    //查单个
    public Man getMan(String name) {
        Connection conn = DButil.getConnection();
        Man man = null;
        try {
            String sql = "select * from man where name = ?";
            PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
            ptmt.setString(1, name);
            ResultSet rs = ptmt.executeQuery();
            if (rs.next()) {
                man = new Man();
                man.setName(rs.getString("name"));
                man.setAge(rs.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return man;
    }


}

---

model层 实体类

package com.example.model;

public class Man {

    private String name ;
    private Integer age ;

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }



}

转载于：http://www.imooc.com/learn/157