19.JDBC和数据库访问.md
1.基本功能：
Java通过JDBC完成：
2.基本类型，通常用最后一种
3.JDBC简介
Java连接SQL例子：
参考：http://blog.chinaunix.net/uid-20726500-id-3876569.html
1.加载驱动
2.获取连接
3.连接实例：
定义：
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566 //DbManage.java
import java.sql.*;
public class DbManage {
public boolean InitDb(){
//初始化驱动
try {
//Class-SQL Server
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//Class-MySQL
//Class.forName("com.mysql.jdbc.Driver");
//Class-Oracle
//Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException ec) {
System.out.println("Error 1: ");
System.out.println(ec.getMessage());
}
catch(Exception e){
System.out.println("Error 2: ");
System.out.println(e.getMessage());
}
//获取数据连接
/*url的填写遵循各自规范
--通用
jdbc:subprotocol:other stuff,其中subprotocol之连接到特定的数据库,sutff不固定
--mysql
jdbc:mysql://hostname:port/databasename
--oracle
jdbc:oracle:thin:@hostname:port:databasename
*/
//url
String url = "jdbc:sqlserver://localhost:49838;databaseName=HAMSDb;IntegratedSecurity=True";
//这里的端口要查看具体电脑,最后一个选项是用Windows验证的方式去连接到数据库服务器，不需要用户名和密码。官方推荐方式
//String user = ""; //用户名
//String pass = ""; //密码
try {
/*获取连接*/
//标准的连接方式：DriverManager.getConnection(url, user, pass);
Connection cn = DriverManager.getConnection(url);
/*使用Statement操作数据库*/
Statement st = cn.createStatement();
//使用结果集查询表结果
ResultSet resultQuery = st.executeQuery("select * from Devices");
// while(resultQuery.next()){
// System.out.println("Key: " + resultQuery.getInt(1) + "Id: " + resultQuery.getString(2));
// }//while
//excute执行，但是这里的返回值boolean中的true和false不代表执行成功或者失败，表示是否有ResultSet对象返回，这点要注意！
/*
boolean b = st.execute("insert into aTest(ID, Name) values(3, 'ccc')");
if (b) {
System.out.println("有ResultSet对象返回，下面可以用ResultSet获取返回的结果集。");
}
*/
} catch (SQLException e) {
System.out.println("Error 3: ");
System.out.println(e.getMessage());
}
return true;
}
}
123456789101112 package com.company.project.jdbctest;
//Print.java
public class Print {
public static void main(String[] args) {
// TODO Auto-generated method stub
//原始方法
DbManage d = new DbManage();
d.InitDb();
}
}
4.excuteUpdate语句
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980 package com.company.project.jdbctest;
//InitDataBase.java
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.*;
public class InitDataBase {
//url
private String url;
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
//forClassName
private String forClassName;
public String getForClassName() {
return forClassName;
}
public void setForClassName(String forClassName) {
this.forClassName = forClassName;
}
//Connection
private Connection conn;
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
//构造
public InitDataBase(){};
//读取配置文件
public void ReadPropertyFile(){
/*读取ini文件获取配置*/
try{
//读取配置文件
Properties p = new Properties();
p.load(new FileInputStream("Properties//mysqlconfig.ini"));
//读取sqlClassForName
setForClassName(p.getProperty("classForName"));
//读取url
setUrl(p.getProperty("url"));
}
catch(Exception e){
e.getMessage();
System.out.println("Load Properties Failed!");
}
}
public void ConnectionSQL(){
try {
/*开始连接数据库*/
ReadPropertyFile();
//加载类
Class.forName(getForClassName());
//获取连接
conn = DriverManager.getConnection(getUrl());
} catch (ClassNotFoundException eof) {
// TODO Auto-generated catch block
eof.printStackTrace();
}
catch (Exception e){
e.printStackTrace();
}
}
}
12345678910111213141516171819202122232425 package com.company.project.jdbctest;
//ExcuteDDL.java
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
//执行DDL语句
public class ExcuteDDL {
public void createTable(String strSql){
//获取连接
InitDataBase idb = new InitDataBase();
idb.ConnectionSQL();
Connection cn = idb.getConn();
try {
Statement st = cn.createStatement();
st.executeUpdate(strSql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.getMessage();
System.out.println("ExcuteDDL Error !");
}
}
}
12345678910111213 package com.company.project.jdbctest;
//Print.java
public class Print {
public static void main(String[] args) {
//ExcuteDDL
ExcuteDDL ed = new ExcuteDDL();
//只执行一次
//ed.createTable("create table excuteddl_test(ID int, NAME varchar(32))");
System.out.println("create table successful!");
}
}
5.PreparedStatement语句
12345678910111213141516171819202122232425262728293031 package com.company.project.jdbctest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
//PrepareStatementTest.java
public class PrepareStatementTest {
public void insertOneH(){
//获取连接
InitDataBase idb = new InitDataBase();
idb.ConnectionSQL();
Connection cn = idb.getConn();
//
String strSql = "insert into excuteddl_test(ID, NAME) values(?, ?)";
try {
PreparedStatement ps = cn.prepareStatement(strSql);
//执行
for (int i = 0; i < 100; i++) {
ps.setString(1, i+"");
ps.setString(2, "name"+i+"");
ps.executeUpdate();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
1234567891011 package com.company.project.jdbctest;
//Print.java
public class Print {
public static void main(String[] args) {
PrepareStatementTest pst = new PrepareStatementTest();
pst.insertOneH();
System.out.println("create table successful!");
}
}
6.使用CallableStatement调用存储过程
