文章目录
- 1.获取连接
- 2.动态获取连接(配置文件获取)
- 3.增删改
- 4.封装工具类JDBCUtils
- 5.封装增删改代码
- 6.select一条数据
- 7.封装查找一条数据的代码在一个方法中
1.获取连接
@Test
public void test1()throws Exception{
String url = "jdbc:mysql:///test";
String user = "root";
String password = "root";
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
2.动态获取连接(配置文件获取)
@Test
public void test2()throws Exception{
InputStream is = Demo01.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
3.增删改
@Test
public void test1()throws Exception{
InputStream is = Demo02.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driver = pros.getProperty("driverClass");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql = "insert into user values(?, ?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, 3);
ps.setString(2, "wangwu");
ps.setString(3, "1234");
ps.setString(4, "王五");
ps.execute();
ps.close();
connection.close();
}
4.封装工具类JDBCUtils
因为每次都要进行连接数据库和释放连接所以直接封装起来直接连接和释放
public class JDBCUtils {
public static Connection getConnection() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
Class.forName(pros.getProperty("driverClass"));
Connection connection = DriverManager.getConnection(pros.getProperty("url"), pros.getProperty("user"), pros.getProperty("password"));
return connection;
}
public static void getClose(Connection connection, PreparedStatement preparedStatement){
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void getClose(Connection connection, PreparedStatement ps, ResultSet resultSet){
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet!= null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5.封装增删改代码
public void Test(String sql, Object ...args)throws Exception{
Connection connection = JDBCUtils.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject((i + 1), args[i]);
}
ps.execute();
JDBCUtils.getClose(connection, ps);
}
可以直接使用
@Test
public void test2()throws Exception{
String sql = "delete from user where id = ?";
Test(sql, 4);
}
6.select一条数据
@Test
public void test1()throws Exception{
Connection connection = JDBCUtils.getConnection();
String sql = "select * from user where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1,1);
ResultSet rs = ps.executeQuery();
if (rs.next()){
int id = rs.getInt(1);
String username = rs.getString("username");
String password = rs.getString("password");
String name = rs.getString(4);
User user = new User(id, username, password, name);
System.out.println(user);
}
JDBCUtils.getClose(connection, ps, rs);
}
因为查找的时候可以用实体类来表示.所以我用了一个User实体类来封装起来结果
public class User {
private int id;
private String username;
private String password;
private String name;
public User() {
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
'}';
}
public User(int id, String username, String password, String name) {
this.id = id;
this.username = username;
this.password = password;
this.name = name;
}
}
7.封装查找一条数据的代码在一个方法中
public User Test(String sql,Object ... args)throws Exception{
Connection connection = JDBCUtils.getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
User user = new User();
if (rs.next()){
for (int i = 0; i < columnCount; i++) {
Object object = rs.getObject(i + 1);
String columnName = rsmd.getColumnName(i + 1);
Field field = User.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(user, object);
}
return user;
}
JDBCUtils.getClose(connection, ps, rs);
return null;
}
@Test
public void test2()throws Exception{
String sql = "select * from user where id = ?";
User test = Test(sql, 1);
System.out.println(test);
}
直接调用,提示一下这里用到了反射去给实例对象赋值,并且通过数据集获得数据集的元数据。