java 操作mysql的创建数据库及数据的增删改查

java 操作mysql的创建数据库及数据的增删改查

1.用java创建数据库，但是这种动态创建数据库是非主流的。一般不会这样操作。

import java.sql.*;public class Test{    public static void main(String[] args) throws Exception    {        Class.forName("com.mysql.jdbc.Driver");        //一开始必须填一个已经存在的数据库        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";           Connection conn = DriverManager.getConnection(url, "root", "123456");        Statement stat = conn.createStatement();        //创建数据库hello        stat.executeUpdate("create database hello");        //打开创建的数据库        stat.close();        conn.close();        url = "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8";        conn = DriverManager.getConnection(url, "root", "123456");        stat = conn.createStatement();        //创建表test        stat.executeUpdate("create table test(id int, name varchar(80))");        //添加数据        stat.executeUpdate("insert into test values(1, '张三')");        stat.executeUpdate("insert into test values(2, '李四')");        //查询数据        ResultSet result = stat.executeQuery("select * from test");        while (result.next())        {            System.out.println(result.getInt("id") + " " + result.getString("name"));        }        //关闭数据库        result.close();        stat.close();        conn.close();    }}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

1. 插入

public int insert()  {      int i=0;      String sql="insert into (表名)(列名1,列明2) values(?,?)";      Connection cnn=getConn();      try{          PreparedStatement preStmt =cnn.prepareStement(sql);          preStmt.setString(1,值);          preStmt.setString(2,值);//或者：preStmt.setInt(1,值);          i=preStmt.executeUpdate();      }      catch (SQLException e)      {          e.printStackTrace();      }      return i;//返回影响的行数，1为执行成功  }  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.更新

public int update  {      int i=0;      String sql="update (表名) set  (列名1)=?,列明2=? where (列名)=？";//注意要有where条件      Connection cnn=getConn();      try{          PreparedStatement preStmt =cnn.prepareStatement(sql);          preStmt.setString(1,(值));          preStmt.setString(2,(值));//或者：preStmt.setInt(1,值);          preStmt.setInt(3,(值));          i=preStmt.executeUpdate();      }      catch (SQLException e)      {          e.printStackTrace();      }      return i;//返回影响的行数，1为执行成功  }  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4.查找

public String select      {          String sql = "select * from (表名) where (列名)=(值)";          Connection cnn = getConn();//此处为通过自己写的方法getConn()获得连接          try          {              Statement stmt = conn.createStatement();              ResultSet rs = stmt.executeQuery(sql);              if(rs.next())              {                  int m1 = rs.getInt(1);//或者为rs.getString(1)，根据数据库中列的值类型确定，参数为第一列                  String m2 = rs.getString(2);              }              //可以将查找到的值写入类，然后返回相应的对象          }          catch (SQLException e)          {              e.printStackTrace();          }          return (相应的值的变量);      }  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

另一种，封装好的查找

import java.sql.SQLException;  public class DBHelper {      public static final String url = "jdbc:mysql://127.0.0.1/student";      public static final String name = "com.mysql.jdbc.Driver";      public static final String user = "root";      public static final String password = "root";      public Connection conn = null;      public PreparedStatement pst = null;      public DBHelper(String sql) {          try {              Class.forName(name);//指定连接类型              conn = DriverManager.getConnection(url, user, password);//获取连接              pst = conn.prepareStatement(sql);//准备执行语句          } catch (Exception e) {              e.printStackTrace();          }      }      public void close() {          try {              this.conn.close();              this.pst.close();          } catch (SQLException e) {              e.printStackTrace();          }      }  }  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

import java.sql.SQLException;  public class Demo {      static String sql = null;      static DBHelper db1 = null;      static ResultSet ret = null;      public static void main(String[] args) {          sql = "select *from stuinfo";//SQL语句          db1 = new DBHelper(sql);//创建DBHelper对象          try {              ret = db1.pst.executeQuery();//执行语句，得到结果集              while (ret.next()) {                  String uid = ret.getString(1);                  String ufname = ret.getString(2);                  String ulname = ret.getString(3);                  String udate = ret.getString(4);                  System.out.println(uid + "\t" + ufname + "\t" + ulname + "\t" + udate );              }//显示数据              ret.close();              db1.close();//关闭连接          } catch (SQLException e) {              e.printStackTrace();          }      }  }  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

5.删除

public int delete()  {      String sql = "delete from (表名) where (列名)=(值)";      int i=0;      Connection conn = getConn();//此处为通过自己写的方法getConn()获得连接      try      {          Statement stmt = conn.createStatement();          i = stmt.executeUpdate(sql);      }      catch (SQLException e)      {          e.printStackTrace();      }      return i;//如果返回的是1，则执行成功;  }