package com.itheima;
import com.itheima.domain.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* Hello world!
*/
public class App {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//----------------------------问题一 start 获取连接不应该用一个创建一个 应该整个连接池 深化一点 连接池配置 也不应该整写死
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/heima349_mybatis", "root", "123");
//------------------------------问题一 end
//------------------------------问题二 start sql语句写在了代码中 不容易修改 最好放在配置文件中
//准备sql 获取prepareStatement
PreparedStatement statement = connection.prepareStatement("select * from USER ");
//------------------------------问题二 end sql语句写在了代码中 不容易修改 最好放在配置文件中
//执行sql //获取 resultSet
ResultSet resultSet = statement.executeQuery();
//------------------------------问题三 得到结果集以后 操作太繁琐 封装 希望直接结果对象
///有关result获取元信息的操作--------------------------------------start
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
System.out.println("多少列:"+columnCount);
//在这个地方遍历的时候 索引从1开始
for (int i = 1; i <=columnCount; i++) {
System.out.print("第"+i+"列---");
String columnTypeName = metaData.getColumnTypeName(i);
System.out.print("列类型名:"+columnTypeName);
String columnName = metaData.getColumnName(i);
System.out.print("列的名字:"+columnName);
String columnClassName = metaData.getColumnClassName(i);
System.out.println("列的所对应java类型名字:"+columnClassName);
}
///有关result获取元信息的操作--------------------------------------end
List<User> users=new ArrayList<>();
//处理结果
while (resultSet.next()){
User user=new User();
Object id = resultSet.getObject("id");
user.setId((Integer) id);
Object username = resultSet.getObject("username");
user.setUsername((String) username);
Object gender = resultSet.getObject("gender");
user.setGender((String) gender);
Object birthday = resultSet.getObject("birthday");
user.setBirthday((java.util.Date) birthday);
Object address = resultSet.getObject("address");
user.setAddress((String) address);
users.add(user);
}
//关闭资源
resultSet.close();
statement.close();
connection.close();
//------------------------------问题三 得到结果集以后 操作太繁琐
System.out.println(users);
}
}
domain实体类
package com.itheima.domain;
import java.util.Date;
public class User {
private int id;
private String username;
private String gender;
private Date birthday;
private String address;
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 getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", gender='" + gender + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
'}';
}
}
package com.itheima.domain;
public class Product {
private int id;
private String name;
private double price;
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 double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
@Override
public String toString() {
return "Product{" +
"id=" + id +
", name='" + name + '\'' +
", price=" + price +
'}';
}
}
缺点总结:
1、数据库链接创建、释放频繁造成系统资源浪费从而影响系统性能,如果使用数据库链接池可解决此问题。
2、Sql语句在代码中硬编码,造成代码不易维护,实际应用sql变化的可能较大,sql变动需要改变Java代码。
3、使用preparedStatement向占有位符号传参数存在硬编码,因为sql语句的where条件不一定,可能多也可能少,修改sql还要修改代码,系统不易维护。
4、对结果集解析存在硬编码(查询列名),sql变化导致解析代码变化,系统不易维护,如果能将数据库记录封装成pojo对象解析比较方便。