1 使用JDBC完成CRUD
(1)导包
创建lib文件夹,new 一个folder
配置文件jdbc.properties放在resource的资源文件夹里面
资源文件夹new 一个 source folder
(2)注册驱动
//加载驱动
Class.forName("com.mysql.jdbc.Driver")
(3) 获取链接
//获取链接
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/webchat", "root", "1234");
(4)获取statement语句
//获取statment语句
Statement st = cn.createStatement();
(5)执行sql语句
String sql="INSERT INTO user (username,password) VALUES ('诸葛亮','卧龙先生')";
st.execute(sql);
(6)释放资源
try {
if(rs!=null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(st!=null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(cn!=null)
cn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
(7)查询所有
Connection cn = JdbcUtil.getCon();
Statement st =null;
ResultSet rs =null;
try {
st=cn.createStatement();
String sql="SELECT * FROM user";
rs=st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("username")+"=="+rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtil.close(cn, st,rs);//释放资源
}
2 抽取工具类
(1)加载驱动&获取链接
public static Properties p = new Properties();
static{
try {
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
p.load(is);
//加载驱动
Class.forName(p.getProperty("driver"));
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getCon(){
/*Connection cn=null;
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/person", "root", "1234");
} catch (Exception e) {
e.printStackTrace();
}*/
Connection cn=null;
try {
//获取链接
cn = DriverManager.getConnection(p.getProperty("url"), p.getProperty("username"),p.getProperty("password"));
} catch (Exception e) {
e.printStackTrace();
}
return cn;
}
(2)释放资源
public static void close(Connection cn,Statement st,ResultSet rs){
try {
if(rs!=null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(st!=null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(cn!=null)
cn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
3 Dao层
(1) 创建实体类
public class User {
private String username;
private String password;
public User() {}
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;
}
}
(2) 创建Dao层接口
public interface UserDao {
public void addUser(User user);
public void deleteUser(String str);
public List<User> findAllUser();
public void updateUser(User user);
}
(3) 实现Dao层接口
public class UserDaoImpl implements UserDao {
@Override
public void addUser(User user) {
Connection cn = JdbcUtil.getCon();
Statement st =null;
try {
st=cn.createStatement();
String sql="INSERT INTO user (username,password) VALUES ('"+user.getUsername()+"','"+user.getPassword()+"')";
st.execute(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtil.close(cn, st,null);
}
}
}
(4) 测试
public class UserDaoImplTest {
private UserDaoImpl userDaoImpl=new UserDaoImpl();
@Test
public void testAdd(){
User user = new User();
user.setUsername("周瑜");
user.setPassword("水师都督");
userDaoImpl.addUser(user);
}
}