8.7学习心得
1. JDBC 的作用
2. 点击 File -> new -> Web Project ->将 mysql-connector-java-5.1.30.jar (用于连接java和mysql数据库关键文件)拷进 WebRoot\WEB-INF\lib 中 -> 右键该文件 -> Build Path -> Add to Build Path。
3. 代码
(1)遍历数据库中数据:
public static void main(String[] args) {
Connection conn = null; //连接数据库
Statement stmt = null; //传递sql语句
ResultSet rs = null; //查询时用
try{
//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");
//提供JDBC连接的url
String url = "jdbc:mysql://localhost:3306/work";
String username = "root";
String password = "123456";
//创建数据库的连接
conn = DriverManager.getConnection(url, username, password);
//创建一个statement
stmt = conn.createStatement();
//执行sql语句
rs = stmt.executeQuery("select * from student");
//处理结果
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getInt("id"));
}
} catch (ClassNotFoundException e){
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭jdbc对象
try {
if(rs != null)
{
rs.close();
}
if(stmt != null)
{
stmt.close();
}
if(conn != null)
{
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
(2)向数据库中添加数据:
public static void main(String[] args) {
//DDL:数据库模式定义语言,关键字:create
//DML:数据操纵语言,关键字:Insert、delete、update
//DQL:数据库查询语言,关键字:select
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String Driver = "com.mysql.jdbc.Driver";
//提供JDBC连接的url
String url = "jdbc:mysql://localhost:3306/itcast";
String username = "root";
String password = "123456";
try{
//加载JDBC驱动程序
Class.forName(Driver);
//创建数据库的连接
conn = DriverManager.getConnection(url, username, password);
//创建一个statement
//pstmt = conn.prepareStatement("insert into grade values('张三', 95, 1)");
pstmt = conn.prepareStatement("insert into grade values(?, ?, ?)");
pstmt.setString(1, "李四");
pstmt.setInt(2, 99);
pstmt.setInt(3, 2);
//执行sql语句
pstmt.executeUpdate();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}finally{
//关闭jdbc对象
try{
if(pstmt != null){
pstmt.close();
}
if(conn != null){
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
(3)Java Properties类(Key, Value)
- 常用的两个方法:
getProperty ( String key),用指定的键在此属性列表中搜索属性。也就是通过参数 key ,得到 key 所对应的 value。
load ( InputStream inStream),从输入流中读取属性列表(键和元素对)。通过对指定的文件(比如说上面的 test.properties 文件)进行装载来获取该文件中的所有键 - 值对。以供 getProperty ( String key) 来搜索。
JDBCUtil 类:
private static String driver;
private static String url;
private static String username;
private static String password;
static{
try{
Properties p = new Properties();
p.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
driver = p.getProperty("Driver");
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
Class.forName(driver);
} catch(IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection CreateConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, Statement stmt, ResultSet rs){
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
test 类:
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
conn = JDBCUtil.CreateConnection();
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from grade");
while(rs.next()){
System.out.println(rs.getString("username"));
System.out.println(rs.getInt("id"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(conn, stmt, rs);
}
}
(4)连接池
dbComPool 类:
//连接池(connection pool)
//就是将Connection对象放入List中,反复使用
//连接池的初始化
//事先放入多个连接对象
//从连接池中获取连接对象
//如果池中有可用连接,则将池中最后一个返回,同时,将该连接从池中remove,表示正在使用
//如果池中无可用连接,则创建一个新的
//关闭连接
//不是真正的关闭,而是将用完的返回去
//连接池对象
private static List<Connection> pool;
//最大连接数
private static final int POOL_MAX_SIZE = 100;
//最小连接数
private static final int POOL_MIN_SIZE = 10;
public dbComPool(){
initPool();
}
//初始化连接池,让池中的连接数达到最小值
public void initPool(){
if(pool == null){
pool = new ArrayList<Connection>();
}
while(pool.size() < POOL_MIN_SIZE){
pool.add(JDBCUtil.CreateConnection());
System.out.println("初始化池,池中连接数:" + pool.size());
}
}
//从池中取连接,连接池中的最后一个(若连接池没地方连接,则添加连接池)
public Connection getConnection(){
Connection conn = null;
if(pool == null)
{
pool.add(JDBCUtil.CreateConnection());
int last_index = pool.size() - 1;
conn = pool.get(last_index);
pool.remove(last_index);
} else {
int last_index = pool.size() - 1;
conn = pool.get(last_index);
pool.remove(last_index);
}
return conn;
}
//将连接放回池中
public void close(Connection conn){
if(pool.size() >= POOL_MAX_SIZE){
try {
if(conn != null){
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
} else {
pool.add(conn);
}
}
test 类:
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
dbComPool dbpool = new dbComPool();
conn = dbpool.getConnection();
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from grade");
while(rs.next()){
System.out.println(rs.getString("username"));
System.out.println(rs.getInt("id"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
dbpool.close(conn);
}
}