增加
/**
* 增加
*/
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不同