package test;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class CRUD {

    /**

     * @param args

     */

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        try {

            create();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    static void create() throws SQLException {

        Connection conn = null;

        Statement st = null;

        PreparedStatement ps =null;//这种statement可以用来过滤数据库查询语句，防止SQL注入

        ResultSet rs = null;

        try {

            conn = JdbcUtils.getConnection();

            //3，Statement用于“运送”sql语句和sql语句执行结果

            String paraSql = “select * from user hwere name=?”;

            //对于上面这种带？的sql可以用下面这个办法来获取statement     

 

            ps=conn.prepareStatement(sql)；//这种创建连接的办法虽然安全但是创建的时候比较耗时

            String paraName = “’’ or 1 or ‘”;

            ps.setString(1,paraName);

            rs=ps.executeQuery();//这样是查询不到结果的，因为没有一个用户名叫做’’ or 1 or ‘

 

 

 

 

            st = conn.createStatement();

        

            String sql = "insert into user(name,birthday,money) values ('name1','1987-01-01','400')";

           

            String sql1 = "update user set money=money+10";

           

            String sql2 = "delete from user where id>5";

            //4,执行sql

            int count = st.executeUpdate(sql1);//增删改全用这个，返回值是受影响的行数

            rs = st.executeQuery("select * from user");

            

            while(rs.next()) {

                //也可以使用列名，使用列号要从1开始

                //for(int i = 1)

                int i = rs.getMetaData().getColumnCount(); //获取列数

                for(int j=1;j<=i;j++)

                System.out.print(rs.getObject(j)+"\t");

                System.out.println();

            }

            System.out.println(count);

        } finally {

            JdbcUtils.free(rs, st, conn);

        }

    }

}

 

package test;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/* 简陋工具类  */

public final class JdbcUtils {

    private static String url="jdbc:mysql://localhost:3306/world";

    private static String user = "root";

    private static String password="mysql";

    static {

        try {

            Class.forName("com.mysql.jdbc.Driver");

        } catch (ClassNotFoundException e) {

            throw new ExceptionInInitializerError(e);

        }

    }

   

    private JdbcUtils() {}

   

    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url,user,password);

    }

   

    public static void free(ResultSet rs,Statement st,Connection conn) {

        try {

            if (rs != null) {

                rs.close();

            }

        }catch(SQLException e) {

            e.printStackTrace();

        }

        finally {

            try {

                if (st != null) {

                    st.close();

                }

            } catch(SQLException e){

                e.printStackTrace();

            } finally {

                if (conn != null)

                    try {

                        conn.close();

                    } catch (SQLException e) {

                        e.printStackTrace();

                    }

            }

        }

    }

}