Java单线程连接SQL server2012数据库[JDBC驱动]

来源：互联网发布：虚拟专用网vpn 软件编辑：程序博客网时间：2024/05/29 07:58

思维导图 – Java通过JDBC驱动连接SQL server数据库

JDBC思维导图

一：文件结构
文件结构图

二：样例代码：

BaseDao.java：

/*Auther: Jason  Time:2016-6-24*/package cs;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;//import com.sun.corba.se.pept.transport.Connection;/* 基本数据库类 */public class BaseDao {    /* 数据库驱动 */    private static final String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";    /* 连接数据库路径 */    private static final String url = "jdbc:sqlserver://localhost:1433;DatabaseName=MyDB";    /* 用户信息 */    private static final String user = "sa";    private static final String password = "chenmeng";    /* 静态块执行加载数据库驱动，只执行一次 */    static {        try {            Class.forName(driver);        } catch (ClassNotFoundException e) {            e.printStackTrace();        }    }    /*     * connect, PreparedStatement, ResultSet 对象声明 PreparedStatement作用于操作符     */    protected Connection conn;    protected PreparedStatement pstmt;    protected ResultSet rs;    /* 功能函数 */    /* 创建connection对象 */    protected void getConnection() {        if (conn == null) {            try {                conn = DriverManager.getConnection(url, user, password);            } catch (SQLException e) {                e.printStackTrace();            }        }    }    /* 获取PSTMT对象, SQL语句作为传参 */    protected void getPreparedStatement(String sql) {        if (conn == null) {            getConnection();        }        try {            pstmt = conn.prepareStatement(sql);        } catch (SQLException e) {            e.printStackTrace();        }    }    /* 没有占位符的SQL查询 */    protected void getResultSet(String sql) {        if (pstmt == null) {            getPreparedStatement(sql);        }        try {            rs = pstmt.executeQuery();        } catch (SQLException e) {        }    }    /* 检查关闭功能 */    protected void close() {        if (rs != null) {            try {                rs.close();                rs = null;            } catch (SQLException e) {                e.printStackTrace();            }        }        if (pstmt != null) {            try {                pstmt.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();                conn = null;            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

Book.java

package cs;public class Book {    /* 类私有属性 */    private int id;    private String bookname;    private String author;    private String publisher;    private int price;    /* 默认构造函数 */    public Book() {        super();    }    public Book(String bookname, String author, int price) {        super();        this.bookname = bookname;        this.author = author;        this.price = price;    }    public Book(int id, String bookname, String author, int price) {        super();        this.id = id;        this.bookname = bookname;        this.author = author;        this.price = price;    }    /* 操作属性方法 */    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getBookName() {        return bookname;    }    public void setBookName(String bookname) {        this.bookname = bookname;    }    public String getAuthor() {        return author;    }    public void setAuthor(String author) {        this.author = author;    }    public String getPublisher() {        return publisher;    }    public void setPublisher(String publisher) {        this.publisher = publisher;    }    public int getPrice() {        return price;    }    public void setPrice(int price) {        this.price = price;    }}

BookDao,java

package cs;import java.sql.SQLException;import java.util.ArrayList;public class BookDao extends BaseDao {    public ArrayList<Book> queryBooksByPublisher(String publisher) {        ArrayList<Book> bookList = new ArrayList<Book>();        String sql = "select * from tbook where publisher=?";        getPreparedStatement(sql);        try {            pstmt.setString(1, publisher);            rs = pstmt.executeQuery();            while (rs.next()) {                /* 循环读取SQL数据 */                int id = rs.getInt("id");                String name = rs.getString("name");                String author = rs.getString("author");                int price = rs.getInt("price");                /* Book初始化 */                Book book = new Book(id, name, author, price);                bookList.add(book);            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            /* 释放资源 */            close();        }        return bookList;    }}

BookTest.java

package cs;import java.util.ArrayList;public class BookTest {    public static void main(String[] args) {        /* 创建books对象数组 */        ArrayList<Book> books = new BookDao()                .queryBooksByPublisher("四川师范大学出版社");        /* 迭代输出books对象中的数据 */        for (Book book : books) {            System.out.println(book.getBookName() + book.getPrice());        }    }}

三：数据库表单

MyDB:create table tbook(    id int primary key,    bookname varchar(20),    author varchar(20),    publisher varchar(50),    price int)

四：环境
1）平台：Win10+ MyEclipse10+ 内置Tomcat6.0服务器；
2）JRE：Java1.8.0环境
3）JDBC驱动：sqljdbc4.jar
4）数据库：SQLserver 2012

五：其他
1）有相关问题可以联系博主，邮箱：369575409@qq.com 欢迎打扰；
2）下片博文预告：servlet + ThreadLocal解决多线程并发问题；
3）今后会新增changelog修改日志；
4）如果有更好的解决方法，请fork；
5）参考资料：
http://blog.csdn.net/stewen_001/article/details/19553173/
http://blog.csdn.net/keenweiwei/article/details/7332261
http://blog.csdn.net/that3/article/details/7584454
http://blog.csdn.net/chenleixing/article/details/44024095
6）版权所有，不得转载！

0 0