1、JDBC的基本应用
1）创建数据库：
create database test;
use test;
2）创建表：
create table student(
id int(4) not null auto_increment,
name varchar(20) default null,
score int(3) default 0,
primary key (id)
);
3）学生类：
package com.yyq;
/**
* Created by gao on 16-4-12.
*/
public class Student {
//学生Id
private int id;
//学生姓名
private String name;
//班级
private String myClass;
//分数
private double score;
//提供一个公共无参数的构造方法
public Student(){
}
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 getMyClass() {
return myClass;
}
public void setMyClass(String myClass) {
this.myClass = myClass;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
}
4）插入一条数据：
package com.yyq;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Created by gao on 16-4-12.
*/
public class DoInsert {
private String dirver = "com.mysql.jdbc.Driver";
private String url="jdbc:mysql://localhost:3306/test";
private String user="root";
private String password="123456";
public boolean addStudent(Student stu){
Connection conn=null;
Statement st=null;
String sql="INSERT INTO student(name,class,score) values('"+stu.getName()+"'," +
"'"+stu.getMyClass()+"',"+stu.getScore()+")";
boolean flag=false;
try {
Class.forName(dirver);
conn= DriverManager.getConnection(url, user, password);
st=conn.createStatement();
int i=st.executeUpdate(sql);
if(i==1){
flag=true;
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return flag;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
DoInsert doinsert=new DoInsert();
Student stu=new Student();
stu.setName("xiaoming");
stu.setMyClass("java");
stu.setScore(98.00);
boolean flag=doinsert.addStudent(stu);
if(flag){
System.out.println("成功插入一条数据!");
}else{
System.out.println("插入数据失败!");
}
}
}
5）查询数据
package com.yyq;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* Created by gao on 16-4-12.
*/
public class DoSelect {
//step2:提供连接数据库所需的dirver,url,user,password
private String dirver = "com.mysql.jdbc.Driver";
private String url="jdbc:mysql://localhost:3306/test";
private String user="root";
private String password="123456";
public List<Student> findAll(){
//声明一个局部的Connection对象
Connection conn=null;
//声明一个局部的Statement 对象
Statement st=null;
//声明一个ResultSet结果集对象
ResultSet rs=null;
//定义SQL语句
String sql="select * from student";
//保存返回结果
List<Student> list=new ArrayList<Student>();
try {
//step1:加载数据库厂商提供的驱动JAR包
Class.forName(dirver);
//step3:通过DriverManager获取一个数据库连接对象
conn= DriverManager.getConnection(url, user, password);
//step4:创建一个Statement对象
st=conn.createStatement();
//step5:执行sql语句
rs=st.executeQuery(sql);
//step6:处理结果集
while(rs.next()){
Student stu=new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setMyClass(rs.getString("class"));
stu.setScore(rs.getDouble("score"));
list.add(stu);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//step7:关闭连接数据库资源
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return list;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
DoSelect select=new DoSelect();
//调用查询的方法
List<Student> list=select.findAll();
for(Student stu:list){
System.out.println("Id:"+stu.getId()+"\t姓名:"+stu.getName()+"\t班级:"+stu.getMyClass()+"\t分数:"+stu.getScore());
}
}
}
6）使用PrepareStatement查询
可以在查询语句中动态加入查询条件。首先，使用Connection的prepareStatement()方法建立好一个预编译的SQL语句——其中参数会变动的部分用“？”来占位。等到需要真正指定参数执行时，再使用相对应的setXXX(int parameterIndex, 值)方法，指定“？”处真正应该有的参数值。
package com.yyq;
import java.sql.*;
/**
* Created by gao on 16-4-12.
*/
public class PreparedSelect {
//step2:提供连接数据库所需的driver，url,user,password
private String dirver = "com.mysql.jdbc.Driver";
private String url="jdbc:mysql://localhost:3306/test";
private String user="root";
private String password="123456";
public Student findById(int id){
//声明一个局部的Connection对象
Connection conn=null;
//声明一个局部的Statement 对象
PreparedStatement pst=null;
//声明一个ResultSet结果集对象
ResultSet rs=null;
//定义SQL语句
String sql="select * from student where id=?";
//保存返回结果
Student stu = null;
try {
//step1:加载数据库厂商提供的驱动JAR包
Class.forName(dirver);
//step3:通过DriverManager获取一个数据库连接对象
conn=DriverManager.getConnection(url, user, password);
//step4:创建一个PreparedStatement对象
pst=conn.prepareStatement(sql);
pst.setInt(1, id);
//step5:执行sql语句
rs=pst.executeQuery();
//step6:处理结果集
if(rs.next()){
stu=new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setMyClass(rs.getString("class"));
stu.setScore(rs.getDouble("score"));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//step7:关闭连接数据库资源
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return stu;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
PreparedSelect ps=new PreparedSelect();
//调用查询方法
Student stu=ps.findById(12);
System.out.println("Id:"+stu.getId()+"\t姓名:"+stu.getName()+"\t班级:"+stu.getMyClass()+"\t分数:"+stu.getScore());
}
}
2、对JDBC操作数据库的工具类进行封装
1）对获得Connection对象进行封装
//DB_Driver
driver = com.mysql.jdbc.Driver
//DB_URL
url = jdbc:mysql://localhost:3306/test
//DB_USERNAME
username = root
//DB_PASSWORD
password = 123456
2）创建ConnectionFactory工程类，用于生产连接对象
package com.yyq.factory;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* 数据库连接对象(Connection)工厂类
*/
public class ConnectionFactory {
private static String DB_DRIVER;
private static String DB_URL;
private static String DB_USERNAME;
private static String DB_PASSWORD;
public ConnectionFactory() {
}
/**
* 在静态代码块中，获得属性文件中的driver,url,username,password
*/
static{
//step1:创建一个Properties对象
Properties pro = new Properties();
//step2:以流的形式读取属性文件中的内容
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
try {
//step3:加载流is到p对象中
pro.load(is);
DB_DRIVER = pro.getProperty("driver");
DB_URL = pro.getProperty("url");
DB_USERNAME = pro.getProperty("username");
DB_PASSWORD = pro.getProperty("password");
}catch (IOException e){
e.printStackTrace();
}
}
/**
* 该方法用来加载驱动，并获得数据库的连接对象
*
* @return 数据库连接对象conn
*/
public static Connection getConnection(){
Connection conn = null;
try {
//加载驱动
Class.forName(DB_DRIVER);
}catch (ClassNotFoundException e){
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
}catch (SQLException e){
e.printStackTrace();
}
return conn;
}
}
3）创建CloseFactory工厂类，用于关闭连接对象
package com.yyq.factory;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Created by gao on 16-4-13.
*/
public class CloseFactory {
//关闭Connection
public static void close(Connection conn){
if (null != conn){
try{
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
//关闭Statemnt
public static void close(Statement stmt){
if (null != stmt){
try{
stmt.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
//关闭ResultSet
public static void close(ResultSet rs){
if (null != rs){
try {
rs.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
//关闭用来执行Select的JDBC资源
public static void close(ResultSet rs, Statement stmt, Connection conn){
close(rs);
close(stmt,conn);
}
public static void close(Statement stmt, Connection conn){
close(stmt);
close(conn);
}
}
4）创建ContrlFactory工厂类，用于执行数据库操作
package com.yyq.factory;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Created by gao on 16-4-13.
*/
public class ContrlFactory {
/**
* 执行select语句
*/
public static ResultSet executeQuery(String sql) throws Exception{
ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
conn = ConnectionFactory.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
}catch (SQLException e){
throw e;
}
return rs;
}
/**
* 执行Insert、Update、Delete语句
*/
public static void executeUpdate(String sql) throws Exception{
Connection conn = null;
Statement stmt = null;
try {
conn = ConnectionFactory.getConnection();
stmt = conn.createStatement();
stmt.executeUpdate(sql);
}catch (SQLException e){
e.printStackTrace();
}finally {
CloseFactory.close(stmt,conn);
}
}
}
5）测试类
package com.yyq;
import com.yyq.factory.CloseFactory;
import com.yyq.factory.ConnectionFactory;
import com.yyq.factory.ContrlFactory;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Created by gao on 16-4-13.
*/
public class JDBCTest {
Connection conn = null;
@Test
public void connectionTest(){
conn = ConnectionFactory.getConnection();
if(null == conn){
System.out.println("未获取数据库连接对象");
}else {
System.out.println("已获取数据库连接对象");
}
}
// @Test
// public void closeTest() throws SQLException {
// connectionTest();
// CloseFactory.close(null,null,conn);
// if(null == conn){
// System.out.println("数据库连接对象已关闭");
// }else {
// System.out.println("数据库连接对象未关闭");
// }
// }
@Test
public void contrlQueryTest() throws Exception {
String sql = "select * from student;";
ResultSet rs = ContrlFactory.executeQuery(sql);
while (rs.next()){
System.out.println("Id："+rs.getInt("id")+"\t姓名："+rs.getString("name")+"\t科目："
+rs.getString("class")+"\t分数："+rs.getInt("score"));
}
}
@Test
public void contrlInsertTest() throws Exception{
String sql = "insert into student(name,score,class) values('sheng',88,'linxu')";
ContrlFactory.executeUpdate(sql);
}
}
6）项目结构图：