Jdbc
Jdbc是Java语言用于访问数据库的应用编程接口,可以通过它访问各类数据库。对应的类库主要分布在java.sql和javax.sql。
数据库常用的类
- DriverManger,是JDBC的管理层。
- Connection接口,是特定的数据库的连接。通过DriverManager类的静态方法的getConnection()方法来获取。
- Statement对象将sql语句发送到数据库中,执行对数据库的数据的检索或者更新,有两个子接口:CallableStatement和PreparedStatement。
- PreparedStatement接口:Statement主要用来执行静态的SQL语句,如果有些操作只是与SQL语句中某些参数有所不同,其余的SQL子句相同,则使用PrepareStatement来提高效率。使用PrepareStatement方法建立好一个预先编译的SQL语句:其中参数会变动的部分会先用“?”作为占位符,等到需要真正指定参数执行时,在这个相对的setXXX指定值的时候,"?"才有真正的参数值。
- ResultSet包含符合SQL语句条件中的所有行,并且提供get()方法来获取当前行中的不同列,并且有next方法来移动到ResultSet的下一行,使下一行成为当前行。通过Statement的相关方法来获取ResultSet对象,通常使用比较多的是executeQuery,执行给定的查询SQL语句,该语句返回单个ResultSet对象;executeUpdate执行给定的SQL语句,该语句可能为insert、update、delete,返回执行成功的条数,返回-1是失败。
获取对应查询结果列的方法:
getXXX(int columnIndex)使用列索引获取值,从列1开始编号
getXXX(String columnLabel)使用列的名称获取值
数据库的使用
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static Connection c = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
//mysql的url:jdbc:mysql://localhost:3306/test
//oracle的url:jdbc:oracle:thin:@localhost:1521:sid/test
//需要注意的是最后的test你你要链接的数据库的数据库的名字
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
//mysql驱动:com.mysql.jdbc.Driver
//oracle驱动:oracle.jdbc.driver.OracleDriver
c = (Connection) DriverManager.getConnection(URL, "root", "123456");
ps = (PreparedStatement) c.prepareStatement("select name from student");
rs = ps.executeQuery();
while(rs.next()) {
String name = rs.getString(1);//根据列的序号来获取信息需要注意是从1开始。
System.out.println(name+"\n");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null) {
try {
ps.cancel();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(c!=null) {
try {
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
//注意点
// 在使用jdbc来连接mysql和oracle数据库的时候要保证它们的服务时打开
//数据库资源使用完毕要及时的关闭
数据库的封装与增删改查
public class DbUtil {
private static final String URL = "jdbc:mysql://localhost:3306/test";
private static Connection c = null;
public static Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
c = (Connection) DriverManager.getConnection(URL, "root", "123456");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return c;
}
public static void closeConnection() {
if(c!=null) {
try {
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void closePrepareStatement(PreparedStatement ps) {
if(ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void closeResultSet(ResultSet rs) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
增删改查方法:
public class method {
private static Connection c = null;
public static void main(String[] args) {
c = DbUtil.getConnection();
//sql语句待改!!!
insertStudent();
//updateStudent();
//deleteStudent();
//selectStudent();
}
public static void insertStudent() {
PreparedStatement ps = null;
try {
ps= (PreparedStatement) c.prepareStatement("insert into admin(name,pass) value(?,?)");
ps.setString(1, "laohe1");//注意这里设置值的序号是按占位符的顺序,从1开始
ps.setString(2, "lao2");
int count = ps.executeUpdate();
if(count!=-1) {
System.out.println("scuess");
}else {
System.out.println("fail");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DbUtil.closeConnection();
DbUtil.closePrepareStatement(ps);
}
}
public static void deleteStudent() {
PreparedStatement ps = null;
try {
ps= (PreparedStatement) c.prepareStatement("delete from admin where id=?");
ps.setInt(1, 2);
int count = ps.executeUpdate();
if(count!=-1) {
System.out.println("scuess");
}else {
System.out.println("fail");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DbUtil.closeConnection();
DbUtil.closePrepareStatement(ps);
}
}
public static void updateStudent() {
PreparedStatement ps = null;
try {
ps= (PreparedStatement) c.prepareStatement("update student set name ='laohe' where id=?");;
ps.setInt(1, 2);
int count = ps.executeUpdate();
if(count!=-1) {
System.out.println("scuess");
}else {
System.out.println("fail");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DbUtil.closeConnection();
DbUtil.closePrepareStatement(ps);
}
}
public static void selectStudent() {
PreparedStatement ps = null;
ResultSet rs =null;
try {
ps= (PreparedStatement) c.prepareStatement("select * from admin");
rs = ps.executeQuery();
while(rs.next()) {
int id=rs.getInt(1);
String name = rs.getString(2);
String password = rs.getString(3);
System.out.println("id:"+id+"\t\t"+"name:"+name+"\t\t"+"age:"+password);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DbUtil.closeConnection();
DbUtil.closePrepareStatement(ps);
DbUtil.closeResultSet(rs);
}
}
}