Addition, deletion and modification of database

Posted by aseaofflames on Wed, 10 Jul 2019 21:16:05 +0200

Construction of Navicat for MySQL database and how to build person table
Import mysql-connector-java-5.0.8-bin.jar package into IDE
Create a directory for the person small project
Writing Specific Codes
Note to import the jar package into the library

The first step is the construction of Navicat for MySQL database and how to build tables with person

Navicat for MySQL is an ideal solution for managing and developing MySQL or MariaDB. It supports a single program and can connect to both MySQL and MariaDB. This fully functional front-end software provides an intuitive and powerful graphical interface for database management, development and maintenance, and provides a comprehensive set of tools for novices and professionals of MySQL or MariaDB. - [Baidu Encyclopedia]

The interface is shown in the figure.

Among them, information_schema, mysql, performance_schema and test are the four databases that come with this software. I have built two new databases, and the naming rule is "db_object".

New person table, field: id, name, age, description (note data type and null value)

It must be emphasized that the main key of this thing, because this has suffered a lot, there are various bug s.
Primary key: To determine the unique identification of a field, the ID is usually set as the primary key, and the automatic increment is set. (fields set as primary keys, must be unique identifiers)

The funny thing is, whether it's int or varchar, length is the most common length (without converting it into binary or something). For example, if the length of varchar is 6, then "Xiaowangcai 666" is the length of 6, which is the maximum value of the data.

The second step is to import the mysql-connector-java-5.0.8-bin.jar package into idea

First, we need to create a new lib folder in the WEB-INF folder, drag the corresponding jar package into the WEB-INF folder, then right-click the mouse to select Add as Library, after a series of OK, you can use the jar package.

Step 3: Create a directory for the person small project

Note that when creating folders in src, it may be a little scared, and stick to the previous com.test, then you can choose Excluded to split them up.

As shown in the figure, the dao folder is red and cannot be created inside. Java class. At this point, the old routine, Mark Directory As, Cancel Excluded

Create the com.test parent directory in turn, and the three subdirectories under it, dao, entity, test.

- dao directory (Data Access Object), data access object is the first object-oriented database interface
- entity directory (entity), entity is a specific software module.
- test directory, final trial test.

Step Four: Writing Specific Codes

- PersonDao file

public class Persondao {
    private final static String DRIVER ="com.mysql.jdbc.Driver";
    private final static String URL = "jdbc:mysql://localhost:3306/db_person";
    private final static String USERNAME = "root";
    private final static String PASSWORD = "root";

First, you must define four private static global variables.

/**
     *Add a person
     * @param person
     * @return boolean
     */
    public static boolean insert(Person person) {
        boolean flag = false;//
        try {
            Class.forName(DRIVER);//Loading database driver
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            /*
            Use the connection interface in the jar package and create the connection object conn through the static method getConnection() of the DriveManager class
            */
            String sql = "INSERT INTO t_person(name,age,description) VALUES(?,?,?)";//Writing sql statements
            PreparedStatement ps = conn.prepareStatement(sql);//// Create the preprocessing object ps
            ps.setString(1, person.getName());
            ps.setInt(2, person.getAge());
            ps.setString(3, person.getDescription());
            if (ps.executeUpdate() > 0) {
                flag = true;
            }
            ps.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

Note that ps.setString(1,per.getName) must be in strict order. In your t_person table, 1, 2, 3 corresponds to name,age,description.

 /**
     * Delete a person
     * @param id
     * @return
     */
   public static boolean delete(int id){
       boolean flag = false;
       try{
           Class.forName(DRIVER);//Loading database driver
           Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//Establishing database connection
           String sql = "DELETE FROM t_person WHERE id="+id;//Writing sql statements
           PreparedStatement ps = conn.prepareStatement(sql);//sql execution statement
           if(ps.executeUpdate() > 0) {
               flag = true;
           }
           ps.close();
           conn.close();

       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return flag;
   }

 /**
     * Change a person by ID
     * @param id
     * @return
     */
    public static boolean update(Person person,int id){
        boolean flag = false;
        try{
            Class.forName(DRIVER);//Loading database driver
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//Connect to the database
            String sql = "UPDATE t_person SET name=?,age=?,description=? WHERE id= "+id;//Writing sql statements
            PreparedStatement ps = conn.prepareStatement(sql);//sql execution statement
            ps.setString(1,person.getName());
            ps.setInt(2,person.getAge());
            ps.setString(3,person.getDescription());
            if(ps.executeUpdate() > 0) {
                flag = true;
            }
            ps.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

/**
     *Find a person by ID
     * @param id
     * @return
     */
    public static Person SelectById(int id){
        Person person = null;
        ResultSet rs = null;
        try{
            Class.forName(DRIVER);//Loading database driver
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//Database Connection
            String sql = "SELECT * FROM t_person WHERE id="+id;//Writing sql statements
            PreparedStatement ps = conn.prepareStatement(sql);//sql execution statement
            rs = ps.executeQuery();//Execute the SQL query in the PrepareStatement object and return the ResultSet object generated by the query
            while (rs.next())//If the current statement is not the last line, the loop is executed
            {
/*
                ResultSet The instance has a pointer to the current row of data. The pointer starts at the front of the first record. The pointer can be moved down by next() method.
*/
               person = new Person();
                person.setId(rs.getInt("id"));
                person.setName(rs.getString("name"));
                person.setAge(rs.getInt("age"));
                person.setDescription(rs.getString("description"));
            }
            ps.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return person;
    }

 /**
     * Search all the people
     * @return
     */
    public static List<Person> select(){
        List<Person> list = new ArrayList<Person>();
        ResultSet rs = null;
        try{
            Class.forName(DRIVER);//Loading database driver
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//Database Connection
            String sql = "SELECT * FROM t_person";//Writing sql statements
            PreparedStatement ps = conn.prepareStatement(sql);//sql execution statement
            rs = ps.executeQuery();
            while (rs.next())
            {
                Person person = new Person();
                person.setId(rs.getInt("id"));
                person.setName(rs.getString("name"));
                person.setAge(rs.getInt("age"));
                person.setDescription(rs.getString("description"));
                list.add(person);
            }
            ps.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
 }

- Person file

public class Person {
    private int id;//Primary key
    private String name;//Full name
    private int age;//Age
    private String description;//describe

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

In particular, when searching for a person by ID

/**
     * The toString() method needs to be rewritten.
     * @return
     */
    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", description='" + description + '\'' +
                '}';
    }
}

-test

public class test {


    public static void main(String[] args){
//        Person person = new Person();
//        person.setAge(0);
//        person.setName("Wu Xiaoyang");
//        person.setDescription("a fool");
//        System. out. println (Persondao. insert (person); //Add a person
//        System.out.println(Persondao.delete(2)); //Delete a person
//        System.out.println(Persondao.update(person,4));
//        Person person =Persondao.SelectById(4);
//        System.out.println(person);
        List<Person> list = Persondao.select();//Find List
        for(int i=0;i<list.size();i++){
            System.out.println(list.get(i));
        }


    }
}

Topics: SQL Database MySQL Navicat

Programmer Think

Addition, deletion and modification of database

The first step is the construction of Navicat for MySQL database and how to build tables with person

The second step is to import the mysql-connector-java-5.0.8-bin.jar package into idea

Step 3: Create a directory for the person small project

Step Four: Writing Specific Codes

Hot Topics