增加
/** * 增加 */ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager .getConnection("jdbc:odbc:mydatasource"); String sql = "insert into student(id,names)values(?,?)"; PreparedStatement prep = con.prepareStatement(sql); prep.setObject(1, 2); prep.setObject(2, "贺某某"); int count = prep.executeUpdate(); if (count > 0) { System.out.println("添加成功~"); } prep.close(); con.close(); } catch (Exception e) { e.printStackTrace(); }
删除
/** * 删除 */ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // 得到数据库的连接 Connection con = DriverManager .getConnection("jdbc:odbc:mydatasource"); // 定义影响行数变量 int count = 0; String sql = "delete from student where id=?"; // 创建执行对象 PreparedStatement prep = con.prepareStatement(sql); // 设置占位符的参数值 prep.setObject(1, 2); // 执行获得影响的行数值 count = prep.executeUpdate(); // 关闭资源 prep.close(); con.close(); if (count > 0) { System.out.println("删除成功!"); } } catch (Exception ex) { ex.printStackTrace(); }
修改
/** * 修改 */ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager .getConnection("jdbc:odbc:mydatasource"); String sql = "update student set names=? where id=?"; PreparedStatement prep = con.prepareStatement(sql); prep.setObject(1, "刘丹"); prep.setObject(2, 2); int count = prep.executeUpdate(); if (count > 0) { System.out.println("更新成功!"); } prep.close(); con.close(); } catch (Exception e) { e.printStackTrace(); }
查询所有
/** * 查询所有 */ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager .getConnection("jdbc:odbc:mydatasource"); String sql = "select * from student"; PreparedStatement prep = con.prepareStatement(sql); ResultSet rs = prep.executeQuery(); while (rs.next()) { System.out.print(rs.getInt(1) + " "); System.out.println(rs.getString(2)); } } catch (Exception e) { e.printStackTrace(); }
根据ID查询
/** * 根据Id查询 */ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager .getConnection("jdbc:odbc:mydatasource"); String sql = "select * from student where id=?"; PreparedStatement prep = con.prepareStatement(sql); prep.setInt(1, 2); ResultSet rs = prep.executeQuery(); while (rs.next()) { System.out.println(rs.getString(2)); } } catch (Exception e) { e.printStackTrace(); }
代码使用方法:
1.以上代码在java项目中的main方法中直接运行即可。
2.不用加jar包。需要在Access中创建数据源(上篇博客中有创建数据源的方法)
3.采用的是建立DSN(原名:Data Source Name 中文名:数据源名称)
4.本文的数据源名称是(mydatasource)
5.详细注释请看删除
创建表(Access中创建):
Access数据库的增删改查和Oracle,Mysql的区别
1.驱动不同
2.连接url不同