对象关系映射虽然比较简单基础,不过却是后边持久层框架的根基,所以还是需要记录一下的
insert的时候出现乱码问题,以前没有遇到过,这次也顺手写一下总结,暂时不放在这里了,怕篇幅太长
将一个对象插入数据库中
package ORM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
*
* 将一个对象插入数据库中
* @author zhuang
*
*/
public class TestJdbcInsert {
static void add(User user) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//使用预编译sql
String sql = "insert into user values(?,?,?)";
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=UTF-8",
"root",
"123");
PreparedStatement ps = c.prepareStatement(sql);
)
{
ps.setInt(1, user.id);
ps.setString(2, user.name);
ps.setInt(3, user.age);
//此处使用execute或者executeUpdate都行
ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
User user = new User(3, "DZ", 23);
add(user);
}
}
把这个User对象对应的数据删除掉
package ORM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 把这个User对象对应的数据删除掉
*
* @author zhuang
*
*/
public class TestJdbcDelete {
static void delete(User user) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//使用预编译sql
String sql = "delete from user where id=?";
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=UTF-8",
"root",
"123");
PreparedStatement ps = c.prepareStatement(sql);
)
{
ps.setInt(1, user.id);
//此处使用execute或者executeUpdate都行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
User user = new User(2, "凌志玲", 33);
delete(user);
}
}
更新这条User对象
package ORM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 更新这条User对象
*
* @author zhuang
*
*/
public class TestJdbcUpdate {
static void update(User user) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//使用预编译sql
String sql = "update user set name=?,age=? where id=?";
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=UTF-8",
"root",
"123");
PreparedStatement ps = c.prepareStatement(sql);
)
{
ps.setString(1, user.name);
ps.setInt(2, user.age);
ps.setInt(3, user.id);
//此处使用execute或者executeUpdate都行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
User user = new User(1, "陈冠希", 37);
update(user);
}
}
//根据id值得到一个user对象
package ORM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* //根据id值得到一个user对象
*
* @author zhuang
*
*/
public class TestJdbcGetById {
static User user;
/**
* 得到user对象的方法
*
* @param id
* @return
*/
static User getUser(int id) {
try {
// 打开驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try (
// 通过驱动获得连接
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc", "root", "123");
Statement s = c.createStatement();) {
String sql = "select * from user where id=" + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
String name = rs.getString("name");
int age = rs.getInt("age");
user = new User(id, name, age);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return user;
}
public static void main(String[] args) {
User u = getUser(1);
System.out.println(u.name);
}
}
把所有的User数据查询出来,转换为User对象后,放在一个集合中返回
package ORM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 把所有的User数据查询出来,转换为User对象后,放在一个集合中返回
* @author zhuang
*
*/
public class TestJdbcList {
static List<User> getList(User user) {
//准备一个集合
List<User> userList = new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//使用预编译sql
String sql = "select * from user";
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=UTF-8",
"root",
"123");
PreparedStatement ps = c.prepareStatement(sql);
)
{
ps.execute();
ResultSet rs = ps.getResultSet();
//获得集合
while(rs.next()) {
//把字段装进User对象
user = new User(rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
//把user对象装进list
userList.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return userList;
}
public static void main(String[] args) {
User user = new User(1, "", 1);
List<User> listUser = getList(user);
System.out.println(listUser);
}
}