数据库连接JDBC

oracle:
driver : oracle.jdbc.driver.OracleDriver
url : jdbc:oracle:thin:@localhost:1521:orcl

mysql:
driver : com.mysql.jdbc.Driver
url : jdbc:mysql://localhost:3306/mychool

 1 package com.dao;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 
 9 public class BaseDao {
10     public static void main(String[] args) throws ClassNotFoundException, SQLException {
11         //加载驱动
12         Class.forName("com.mysql.jdbc.Driver");
13         //获取连接
14         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool", "root", "123");
15         //操作状态
16         Statement stm = conn.createStatement();
17         //执行SQL
18         ResultSet rs = stm.executeQuery("select * from student");
19         //处理结果
20         while (rs.next()){
21             int id = rs.getInt("id");
22             String name = rs.getString("name");
23             String city = rs.getString("city");
24             int age = rs.getInt("age");
25             System.out.println(id+"\t"+name+"\t"+city+"\t"+age);
26         }
27         //释放资源
28         rs.close();
29         stm.close();
30         conn.close();
31     }
32 }

BaseDao

  1 package com.dao;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.sql.Statement;
  9 import java.util.ResourceBundle;
 10 /**
 11  * 操作数据库的基类
 12  * */
 13 public class BaseDao {
 14     private static final String DRIVER;
 15     private static final String URL;
 16     private static final String USER;
 17     private static final String PASSWORD;
 18     static {
 19         ResourceBundle bundle = ResourceBundle.getBundle("db");
 20         DRIVER = bundle.getString("driver");
 21         URL = bundle.getString("url");
 22         USER = bundle.getString("username");
 23         PASSWORD = bundle.getString("password");
 24     }
 25     private static Connection conn;
 26     private static Statement stm;
 27     private static PreparedStatement pstm;
 28     private static ResultSet rs;    
 29     /**
 30      * 获取数据库连接
 31      * */
 32     public boolean getConnection(){
 33             try {
 34                 //加载驱动
 35                 Class.forName(DRIVER);
 36                 //获取连接
 37                 conn = DriverManager.getConnection(URL, USER, PASSWORD);    
 38             } catch (ClassNotFoundException e) {
 39                 e.printStackTrace();
 40                 return false;
 41             } catch (SQLException e) {
 42                 e.printStackTrace();
 43                 return false;
 44             }    
 45         return true;
 46     }
 47     /**
 48      * 增删改
 49      * */
 50     public int executeUpdate(String sql, Object[] params){
 51         int updateRows = 0;
 52         try {
 53             pstm = conn.prepareStatement(sql);
 54             //填充占位符
 55             for(int i=0; i<params.length; i++){
 56                 pstm.setObject(i+1, params[i]);
 57             }
 58         updateRows = pstm.executeUpdate();
 59         } catch (SQLException e) {
 60             e.printStackTrace();
 61             updateRows = -1;
 62         }
 63         return updateRows;
 64     }
 65     /**
 66      * 查询
 67      * */
 68     public ResultSet executeQuery(String sql, Object[] params){
 69         try {
 70             pstm = conn.prepareStatement(sql);
 71             //填充占位符
 72             for(int i=0; i<params.length; i++){
 73                 pstm.setObject(i+1, params[i]);
 74             }
 75             rs = pstm.executeQuery();
 76         } catch (SQLException e) {
 77             e.printStackTrace();
 78         }
 79         return rs;
 80     }
 81     /**
 82      * 释放资源
 83      * */
 84     public void close(){
 85         if (rs!=null){
 86             try {
 87                 rs.close();
 88             } catch (SQLException e) {
 89                 e.printStackTrace();
 90             }
 91             rs = null;
 92         }
 93         if (stm!=null){
 94             try {
 95                 stm.close();
 96             } catch (SQLException e) {
 97                 e.printStackTrace();
 98             }
 99             stm = null;
100         }
101         if (pstm!=null){
102             try {
103                 pstm.close();
104             } catch (SQLException e) {
105                 e.printStackTrace();
106             }
107             psrm=null;
108         }
109         if (conn!=null){
110             try {
111                 conn.close();
112             } catch (SQLException e) {
113                 e.printStackTrace();
114             }
115             conn = null;
116         }
117     }
118     
119     
120 }
121 
122 
123 注意:调用方法时,先判断是否拿到connection
124     public void getNewsList() {
125         String sql = "select * from news_detail";
126         Object[] params = {};
127 ---->    if (this.getConnection()){
128             ResultSet rs = this.executeQuery(sql, params);
129             //处理执行结果
130             try {
131                 while (rs.next()){
132                     int id = rs.getInt("id");
133                     String title = rs.getString("title");
134                     System.out.println(id+"\t"+title);
135                 }
136             } catch (SQLException e) {
137                 // TODO Auto-generated catch block
138                 e.printStackTrace();
139             } finally {
140 ---->            this.close();
141             }
142         }
143         
144     }

JDBC 使用Oracle 序列保存数据
　　方法一:
　　　　String sql = "insert into t_user(id,username,password) values(seq_t_user.nextval,?,?)";
　　　　Object[] params = {"王五", "123456"};
　　　　===> seq_t_user.nextval 为id列自增序列
　　方法二:
　　　　使用 pstm.setObject();方法

 

posted @ 2017-06-19 22:27  好奇害死猫+1  阅读(219)  评论(0)    收藏  举报