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，则执行成功;  
}