利用db.properties配置文件形式连接数据库
一、JDBC连接数据库准备工作:
1.工程结构:
2.数据库建表:
create table user(
id int auto_increment primary key,
username varchar(50),
password varchar(50)
);
insert into user(username,password) value("小明",123);
insert into user(username,password) value("小王",456);
3.配置文件的编写
文件名db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/database?userUnicode=true&characterEncoding=UTF-8
username=root
password=
二、JDBC连接数据库
a)实体类的代码演示:
package com.database.entity;
public class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + "]";
}
}
b)为了方便我们写一个工具类,方便以后调用,具体代码实现如下:
package com.database2.util;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
*
* 封装一个DBUtil
*
* @author MrGuo522
*
*/
public class DBUtil {
// 1.定义变量
private static Connection conn;
private static String username;
private static String url;
private static String password;
private static String driver;
static {
// 2.读取属性文件
Properties prop = new Properties();
try {
// 3.通过File IO流读取配置文件
FileInputStream in = new FileInputStream("config/db.properties");
prop.load(in);
in.close();
// 4.接收配置文件的属性值
driver = prop.getProperty("driver");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = "";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
*
* @Description: 对外提供连接的方法
* @param : void
* @author : Administrator
*/
public static Connection getConnection(Connection conn) {
if (conn == null) {
try {
// 1.注册驱动
Class.forName(driver);
// 2.创建连接
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return conn;
}
/**
*
* @Description: 对外提供关闭连接的方法
* @param : void
* @author : Administrator
*/
public static void close(Connection conn, PreparedStatement prep,ResultSet rs) {
if (prep != null) {
try {
prep.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
prep = null;
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
}
}
三、连接测试
代码实现如下:
package com.database2.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import com.database2.util.DBUtil;
/**
*
* @author MrGuo522
*/
public class DataBaseTest {
// 创建连接对象
Connection conn = null;
PreparedStatement prep = null;
Statement st = null;
ResultSet rs = null;
@Test
// 测试添加
public void add() {
try {
// 1.通过工具类调用连接的方法
conn = DBUtil.getConnection(conn);
// 2.通过连接创建PrepareStatement对象,执行sql语句
String sql = "insert into user (username,password) value('测试数据',123)";
prep = conn.prepareStatement(sql);
// 3. 完成操作
prep.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn, prep,rs);
}
}
@Test
// 测试删除
public void delete() {
try {
// 1.通过工具类调用连接的方法
conn = DBUtil.getConnection(conn);
// 2.通过连接创建PrepareStatement对象,执行sql语句
String sql = "delete from user where id=1";
prep = conn.prepareStatement(sql);
// 3. 完成操作
prep.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn, prep,rs);
}
}
@Test
// 测试修改
public void update() {
try {
// 1.通过工具类调用连接的方法
conn = DBUtil.getConnection(conn);
// 2.通过连接创建PrepareStatement对象,执行sql语句
String sql = "update user set username='测试修改' where id=1";
prep = conn.prepareStatement(sql);
// 3. 完成操作
prep.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn, prep,rs);
}
}
@Test
// 测试查询
public void selectAll() {
try {
// 1.通过工具类调用连接的方法
conn = DBUtil.getConnection(conn);
// 2.通过连接创建Statement对象,执行sql语句
st = conn.createStatement();
String sql = "select * from user";
// 3.查询结果返回结果集
rs=st.executeQuery(sql);
// 4.遍历结果集
while(rs.next()){
System.out.println(rs.getString("username")+"::"+rs.getString("password"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn, prep, rs);
}
}
}