Analysis of JDBC

 take the example of connecting MS SQL Server      steps: 1 Creating a project 2 Drive Jar package for import Database 3 Create four packages below SRC DB: A tool class to connect to a database connecting to a database requires four conditions: 1, driver_string, connection database driver2. Ur_string,url address, and set which database to connect to3. Uer_string,user User name4. Pa_string,password Passwordand 3 steps　　1 Loading the database driver string2 Obtaining and connecting to the database static3 ways to turn off database connections static because the method is static all when we need to get a connection, we can directly call through the class name. Method name directly DTO: Data transfer model If there is a table in the database, then there is a DTOThe type of each DTO field and the number of its fields are consistent with the number in the corresponding table and its typeExample: People table (PID pname psex)There are also three properties in a DTO  DAO: Adding and deleting and changingto add,1 Getting a connection to the database2 Preparing SQL statements3 getting command to execute SQL4 Assigning a value to a question mark5 Execute SQL6 Closing the connection7 Releasing ResourcesExample: 1. Database connection Fields

<strong>private static final String driver_string= "Com.microsoft.sqlserver.jdbc.SQLServerDriver";    private static final String ur_string= "jdbc:sqlserver://localhost:1433;" +            "databasename=javateam; Integratedsecurity=true; ";    private static final String uer_string= "sa";    private static final String pa_string= "joy19940521";    Load the database-driven static method    static{        try {            class.forname (driver_string);        } catch (ClassNotFoundException e) {            //TODO auto-generated catch block            e.printstacktrace ();        }    } </strong>

2. How to get Connected

<strong>public static Connection getconnection () {        Connection connection=null;        try {            connection=drivermanager.getconnection (ur_string, uer_string, pa_string);        } catch (SQLException e) {            //TODO auto-generated catch block            e.printstacktrace ();        }                return connection;    } </strong>

3. How to close the connection

<strong>public Connection dropconnection (Connection Connection, Preparedsta                Tement PreparedStatement, ResultSet ResultSet) {if (connection!=null) {try {            Connection.close ();            } catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();            }}if (Preparedstatement!=null) {try {preparedstatement.close ();            } catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();            }}if (Resultset!=null) {try {resultset.close ();            } catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();            }} return null; }</strong> 

4. Adding and deleting changesare implemented separately by different methods. (1) Add

public static void Add (People people) {        //1, Access and database connection        connection = Dbconnection.getcconnection ();        2. Prepare SQL statement        String sql = "INSERT into people (Pname,psex) VALUES (?,?)";        3, prepare the state, get Execute SQL command        try {            pstatement = connection.preparestatement (sql);            4            . Assign a value of pstatement.setstring (1, People.getpname ()) to the question mark;            Pstatement.setstring (2, People.getpsex ());                        int i = Pstatement.executeupdate ();            if (i>0) {                System.out.println ("Add Success"),            }        } catch (SQLException e) {            //TODO auto-generated catch Block            e.printstacktrace ();        } finally{            dbconnection.close (connection, pstatement, null);        }    }

(2) Delete

public static void Delete (int pid) {        connection = dbconnection.getcconnection ();        String sqlString = "Delete from people where pid=?";        try {            pstatement = connection.preparestatement (sqlString);            Pstatement.setint (1, PID);                        Int J = pstatement.executeupdate ();            if (j>0) {                System.out.println ("Delete succeeded")}        } catch (SQLException e) {            //TODO auto-generated catch Block            e.printstacktrace ();        } finally{            dbconnection.close (connection, pstatement, null);        }        }

(3) Update

public static void Update (String pname,string psex,int pid) {        connection=dbconnection.getcconnection ();        String sql = "Update people set pname=?,psex=?" where pid =? ";                try {            pstatement = connection.preparestatement (sql);            Pstatement.setstring (1, pname);            Pstatement.setstring (2, psex);            Pstatement.setint (3, PID);                            int y = pstatement.executeupdate ();                        if (y>0) {                System.out.println ("Update succeeded"),            }        } catch (SQLException e) {            //TODO auto-generated catch Block            e.printstacktrace ();        } finally{            dbconnection.close (connection, pstatement, null);        }            }

(4) Enquiry

According to the PID query public static people FindByID (int pid) {people people = NULL;                Connection = Dbconnection.getcconnection ();        String sql = "SELECT * from people where PID =?";            try {pstatement = connection.preparestatement (sql);            Pstatement.setint (1, PID);            Execution RSet = Pstatement.executequery ();                Iterate over the result set while (Rset.next ()) {int id = rset.getint ("pid");                String name = rset.getstring ("PName");                String sex = rset.getstring ("Psex");            Generate ID Query Object people = new people (ID, name, sex);        }} catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();        }finally{Dbconnection.close (Connection, pstatement, RSet);    } return people;        }//query table so content public static list<people> FindAll () {people people = NULL; List<people> list = new arraylist<people> ();                Connection = Dbconnection.getcconnection ();                String sql = "SELECT * Form People";            try {pstatement = connection.preparestatement (sql);                        RSet = Pstatement.executequery ();                while (Rset.next ()) {int id = rset.getint ("pid");                String name = rset.getstring ("PName");                String sex = rset.getstring ("Psex");                Generate ID Query Object people = new people (ID, name, sex);            List.add (people);        }} catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();        }finally{Dbconnection.close (Connection, pstatement, RSet);    } return list; }

Analysis of JDBC