一、概述
思路分析:
使用JDBC操作新闻信息
使用JDBC连接数据库
使用JDBC增删改查数据
使用配置文件保存数据库信息
使用JNDI连接数据库
学习方法:
JDBC操作数据库的步骤比较固定。
各接口、类和方法需要记忆,查询帮助文档可以帮助记忆。
JDBC是Java数据库连接技术(Java DataBase Connectivity),能实现Java程序对各种数据库的访问。
由一组使用Java语言编写的类和接口(JDBC API)组成,它们位于java.sql以及javax.sql中。
二、JDBC查询学生明细表
需求说明:使用JDBC操作数据中学生明细表,查询学生信息并在控制台正确显示。
实现思路:创建类NewsDao,使用JDBC编写查询学生信息的方法,将学生信息在控制台打印输出。
创建student类:
public class Student
{
private int stu_id;
private String stu_name;
private String gender;
private String phone;
private String address;
private String email;
private String identityCard;
@Override
public String toString()
{
return "Student{" +
"stu_id=" + stu_id +
", stu_name='" + stu_name + '\'' +
", gender='" + gender + '\'' +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", email='" + email + '\'' +
", identityCard='" + identityCard + '\'' +
'}';
}
public int getStu_id()
{
return stu_id;
}
public void setStu_id(int stu_id)
{
this.stu_id = stu_id;
}
public String getStu_name()
{
return stu_name;
}
public void setStu_name(String stu_name)
{
this.stu_name = stu_name;
}
public String getGender()
{
return gender;
}
public void setGender(String gender)
{
this.gender = gender;
}
public String getPhone()
{
return phone;
}
public void setPhone(String phone)
{
this.phone = phone;
}
public String getAddress()
{
return address;
}
public void setAddress(String address)
{
this.address = address;
}
public String getEmail()
{
return email;
}
public void setEmail(String email)
{
this.email = email;
}
public String getIdentityCard()
{
return identityCard;
}
public void setIdentityCard(String identityCard)
{
this.identityCard = identityCard;
}
}
获得连接:
url为地址,user为用户名,pwd为密码
public static Connection getConn()
{
try
{
Class.forName(driver);
return DriverManager.getConnection(url, user, pwd);
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
return null;
}
建立连接,查询结果为ResultSet类:
public static ResultSet query(String sql, Object... params)
{
Connection conn = getConn();
PreparedStatement pst = null;
ResultSet rs = null;
try
{
pst = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++)
{
pst.setObject(i + 1, params[i]);
}
rs = pst.executeQuery();
}
catch (SQLException e)
{
e.printStackTrace();
}
return rs;
}
释放资源
public static void close(Connection conn, PreparedStatement pst, ResultSet rs)
{
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (pst != null)
{
try
{
pst.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (rs != null)
{
try
{
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
测试:
@Test
public void query() throws Exception
{
BaseDao dao=new BaseDao();
String sql="select * from student where stu_id=? or stu_name like ?";
ResultSet rs=dao.query(sql,1014,"李%");
ArrayList<Student> stuList=new ArrayList<>();
while(rs.next())
{
Student s=new Student();
s.setStu_id(rs.getInt("stu_id"));
s.setStu_name(rs.getString("stu_name"));
s.setPhone(rs.getString("phone"));
s.setIdentityCard(rs.getString("identityCard"));
s.setAddress(rs.getString("address"));
s.setEmail(rs.getString("email"));
s.setGender(rs.getString("gender"));
stuList.add(s);
}
for (Student student:stuList)
{
System.out.println(student);
}
dao.close(null,null,rs);
}