jdbc连接mysql数据库和一套增删改查

来源：互联网发布：java的thread.sleep 编辑：程序博客网时间：2024/05/21 18:33

JDBC笔记

1.新建项目（java）JDBCDome

2.新建包（lib）

导入两个JAR

（1）连接数据库

（2）测试的JAR@Test

3.新建一个包jdbc.pojo

User实体类

userName

passWord

set,get方法

4.新建一个包jdbc.connection

数据库链接类Connection

//数据驱动

private String driver="com.mysql.jdbc.Driver";

//链接本机数据库

private String url="jdbc:mysql://localhost:3306/数据库";

private String userName="数据库名";

private String passWord="数据库密码";

private Connection connection=null;

public Connection getConnection(){

try {

Class.forName(driver);

connection= DriverManager.getConnection(url,userName,passWord);

} catch (ClassNotFoundException e) {

e.printStackTrace();

System.out.println("数据库加载错误");

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据库加载错误");

}

return connection;

}

数据资源类jdbcDataSource类

类里边是数据交互内容

增删改查

JdbcConnectin jdbcConnectin=new JdbcConnectin();

Connection connection=jdbcConnectin.getConnection();

PreparedStatement ps=null;//执行sql语句

//数据插入方法

public void insertUser(User user){

String insertUserSql="insert into db_user

(user_name,password) values(?,?)";

try {

ps=connection.prepareStatement(insertUserSql);

ps.setString(1, user.getUserName());//=?(1)

ps.setString(2, user.getPassWord());

ps.executeUpdate();//刷新数据库

} catch (SQLException e) {

e.printStackTrace();

System.out.println("插入用户错误");

}

finally {

try {

ps.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("预编译语句编译错误");

}

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("关闭数据连接错误");

}

/**

* 根据id删除用户

* @param id

public void deleteUser(Integer id) {

String deleteUserSql = "delete from user where id=?";

try {

ps = connection.prepareStatement(deleteUserSql);

ps.setInt(1, id);

ps.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据插入失败");

} finally {

if (ps != null) {

try {

ps.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("资源关闭异常");

}

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据库连接关闭异常");

}

/**

* 更新用户信息

* @param user

public void updateUser(User user) {

String updateUserSql = "update user set user_name=?,pass_word=?where id=?";

try {

ps = connection.prepareStatement(updateUserSql);

ps.setString(1, user.getUserName());

ps.setString(2, user.getPassWord());

ps.setInt(3, user.getId());

ps.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据插入失败");

} finally {

if (ps != null) {

try {

ps.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("资源关闭异常");

}

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据库连接关闭异常");

}

public List<User> findAllUser() {

List<User> users = new ArrayList<User>();

String selectAllUserSql = "SELECT *from user";

try {

ps = connection.prepareStatement(selectAllUserSql);

ResultSet rs = ps.executeQuery();

while (rs.next()) {

User user = new User();

user.setId(rs.getInt(1));

user.setUserName(rs.getString(2));

user.setPassWord(rs.getString(3));

users.add(user);

}

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据插入失败");

} finally {

if (ps != null) {

try {

ps.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("资源关闭异常");

}

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据库连接关闭异常");

}

return users;

}

4.进行程序测试

建包jdbc.test

Test类

//加载数据源

JdbcDataSource jdbcDataSource=new JdbcDataSource();

@Test//测试方法执行入口

public void findAllUser(){

List<User> users=new ArrayList<>();

users=jdbcDataSource.findAllUser();

for(User u:users){

System.out.println(u.getId());

}

@Test

public void updateUser(){

User user=new User();

user.setId(3);

user.setUserName("xiaohuang");

user.setPassWord("1212");

jdbcDataSource.updateUser(user);

}

@Test

public void deleteUser(){

int userId=2;

jdbcDataSource.deleteUser(userId);

}

@Test

public void insert(){

User user =new User();

user.setUserName("xiaoming");

user.setPassWord("123");

jdbcDataSource.insertUser(user);

User user =new User("xiaohua","123");

jdbcDataSource.insertUser(user);

}

5.一个数据流程结束

阅读全文

0 0