一、了解几个概念(了解)
1.DriverManager
1)必须知道它是管理数据库的所有驱动程序
2)它的所有方法都是静态的,其中最重要的是getConnection()方法,我们可以通过它获取一个数据库的连接对象。
2.Connection
数据库连接对象
3.Resultset
1)用来暂时存放查询结果的一个对象
2)最重要的方法时next()方法,该方法将指针下移一行
4.Statement和preparedStatement的区别
它俩的区别主要是PrepareStatement把sql语句中的变量抽取出来了,它俩可以互相替换使用。
例子:
String sql = "select * from users where username= '"+username+"' and userpwd='"+userpwd+"'";
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
String sql= "select * from users where username=? and userpwd=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, userpwd);
rs= pstmt.executeQuery();
5.execute、executeQuery和executeUpdate的区别
1)execute执行增删改查操作
2)executeQuery执行查询操作,返回的正是ResultSet,再对ResultSet对象进行遍历,即可查询结果。
3)executeUpdate执行的是增删改操作
JAVA操作mysql实例:
说明:对数据库db5中的test表进行操作,test表中有三个字段,分别是name,age,sex
public classtest2 {private static String url = "jdbc:mysql://localhost:3306/db5";private static String user = "root";private static String password = "123456";private static String DriverName = "com.mysql.jdbc.Driver";public staticConnection GetConn() {
Connection conn= null;try{
Class.forName(DriverName);
conn=DriverManager.getConnection(url, user, password);
System.out.println("数据库连接成功");
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}finally{returnconn;
}
}public static void insert() throwsSQLException {
Connection conn=GetConn();//String sql = "insert into test(name,age,sex) values ('yj1',1,'f')";//Statement statement = conn.createStatement();//statement.executeUpdate(sql);
String sql = "insert into test(name,age,sex) values (?,?,?)";
PreparedStatement preparedStatement=conn.prepareStatement(sql);
preparedStatement.setString(1, "yj2");
preparedStatement.setInt(2, 4);
preparedStatement.setString(3, "f");
preparedStatement.executeUpdate();
conn.close();
}public static void select() throwsSQLException {
Connection conn=GetConn();
String sql= "select * from test";
Statement statement=conn.createStatement();
ResultSet set=statement.executeQuery(sql);
String name= null;
String sex= null;intage;//next 看是否有下一个数据
while(set.next()) {
name= set.getString(1);
age= set.getInt(2);
sex= set.getString("sex");
System.out.println(name+ age +sex);
}
conn.close();
}public static void update() throwsSQLException{
Connection conn=GetConn();
String sql= "update test set age=? where name = ?";
PreparedStatement preparedStatement=conn.prepareStatement(sql);
preparedStatement.setInt(1,200);
preparedStatement.setString(2,"yj1");
preparedStatement.executeUpdate();
conn.close();
}public static void delete() throwsSQLException{
Connection conn=GetConn();
String sql= "delete from test where name = 'yj1'";
Statement st=conn.createStatement();
st.executeUpdate(sql);
conn.close();
}public static void main(String[] args) throwsSQLException {
}
}